• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Junilu Lacar
  • Paul Clapham
  • Knute Snortum
Saloon Keepers:
  • Stephan van Hulst
  • Ron McLeod
  • Tim Moores
  • salvin francis
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Vijitha Kumara

Inserting StringArray content into database  RSS feed

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm attempting to insert JSON that i have used regex on into my database. This is the current format of my ArrayList:

[[52, 60, 0, 1, 1471096800], [47, 52, 1, 0, 1471701600]

This is my first method that reads in from the API:

private String getURLContents(String URL) {
   // private String getURLContents(String URL) {

       try {
           HttpClient client = HttpClients.createDefault();
           HttpUriRequest request;
           request = RequestBuilder.get()
                   .setUri(URL)
                   .setHeader(HttpHeaders.ACCEPT, "application/json")
                   .setHeader(new BasicHeader("X-Mashape-Key", "vK2kCmhAkymshEpUQu5KtLNKmxawp15d1uPjsnWC0nehykWE5Y"))
                   .build();

           HttpResponse response = client.execute(request);
           String text = EntityUtils.toString(response.getEntity());
           


           return text;

       } catch (IOException ex) {
           Logger.getLogger(MashapeClient.class.getName()).log(Level.SEVERE, null, ex);
       }
       return null;

   }



And this is my second method that applies the regex to only leave values in my arraylist:

private static final Pattern FIXTURES_REGEX =
           Pattern.compile("\\{\"fixture_id\".*?\"homeTeam_id\"\"?\\d+\"?),\"awayTeam_id\"\"?\\d+\"?),.*?\"goalsHomeTeam\"\"?\\d+\"?),\"goalsAwayTeam\"\"?\\d+\"?),.*?\"firstHalfStart\"\"?\\d+\"?)");

   public List<String[]> getLiveFixtures() {
       //String[] words = (getURLContents("https://api-football-v1.p.mashape.com/fixtures/live"));
       String text = getURLContents("https://api-football-v1.p.mashape.com/fixtures/team/52");

      // matches JSON to regex pattern to return only values
       Matcher m = FIXTURES_REGEX.matcher(text);
       
       List<String[]> toReturn = new ArrayList<>();
           while (m.find()) {
               String[] thisFixture = new String[5];
               for (int i = 0; i < thisFixture.length; i++)
                   thisFixture[i] = m.group(i+1).replaceAll("\"", "").trim();      // trimming any brackets or spaces.
               toReturn.add(thisFixture);
               }
               return toReturn;
       
           }


This is how ive started my insert data method:

private void insertFixtures(List<String[]> fixtures) throws SQLException{
String query = "INSERT INTO games (team1_id, team2_id, score1, score2, created_at) VALUES (? ,?, ?, ?, ? )";
Connection con = DBConnector.connect();
PreparedStatement stmt = con.prepareStatement(query);//prepare the SQL Query
   

Im unsure about how to insert this information, as i keep getting problems with the format of my arraylist. Because all the values inside the list are integers have i done this correctly?

Many Thanks
Luke
 
Saloon Keeper
Posts: 5330
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What kind of problems? What is or is not working?
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As far as my ressearch suggests i want to use something similar to the code below:

for (int k = 0; k < fixtures.size()-1; k++) {              
       stmt.setString(1,d.get(k).getResources()); // resource  
       stmt.setString(2,d.get(k).getActivity()); // activity  
       stmt.setInt(3, d.get(k).getEvent_id()); // event id  
       stmt.addBatch();  
   }  

Does this seem like the correct method? I am unsure exactly where this user got .getResouces from and what it does however. Im assuming its a call to another class with a getter and setter?
Apologies im very new to coderanch and also Java.
 
Tim Moores
Saloon Keeper
Posts: 5330
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, questions about a particular piece of code are best asked of the person who wrote the code.

getResources and getActivity look like Android code, though, and since they return objects that are not strings, this would not work here. Are you specifically asking about Android?

But in general, yes - that is how you would bundle several insert statements into one DB call.
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not using android no. I have a Java FX GUI and im using DB Browser for SQL Lite. I think you're right, theres limited help you can give me. I just dont know how best to approach this. My knowledge of working with ArrayLists is very limited,
and because i want to insert 5 values into one line of the database i dont know how to go about it

Thanks for your help however
 
Tim Moores
Saloon Keeper
Posts: 5330
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
5 values into one record? Then a batch is not the right approach, as that would create 5 records. Does the table have 5 fields for the values? Or are you looking for a way to put all values into a single field?
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have a look at my SQL statement, with the example of whats in my arraylist, 52, 60, 0, 1, 1471096800

52 is the team1 id, 60 team2 id, 0 score1, 1 score 2 and the final number a timestamp. I have the 5 different fields yes. Is it a problem all of these ints being stored in a String arraylist?
 
Tim Moores
Saloon Keeper
Posts: 5330
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I see, there are multiple sets of 5 values. So you would need multiple DB inserts, and thus batching.

Whether having ints as strings is a problem depends on what type is used in the DB - if that's an int, then you need to insert an int. Can you post the table's SQL definition?
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
All of the values in the table are ints so yes i will have to convert it to an int arraylist. Im unsure how i would do this.

CREATE TABLE `games` (
`team1_id` INTEGER,
`team2_id` INTEGER,
`score1` INTEGER,
`score2` INTEGER,
`created_at` INTEGER
);
 
Tim Moores
Saloon Keeper
Posts: 5330
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why an array list? The code you posted uses arrays, which seems sufficient.
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've tried converting it into an array with:





But i get given the error java.lang.ArrayStoreException
 
Sheriff
Posts: 21689
101
Chrome Eclipse IDE Java Spring Ubuntu VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's because fixtures does not contain Integer but String[] elements. You can't use toArray with an incompatible type and expect an automagic mapping. You could use streams, but then the question is how to map String[] to Integer.

Example:
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there some way to rewrite this function to return an int instead?



I have issues trimming and removing the code with an integer thisFixture and toReturn :



 
Tim Moores
Saloon Keeper
Posts: 5330
143
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It can't possibly return in int - currently it returns a varied number of 5 values. Or do you mean List<Integer[]>? You can convert a String to an int using Integer.parseInt, provided there are no extra characters in the string.

If the web service returns JSON, why are you using regexps to handle it? That's tricky to get right and error-prone; use a JSON library instead.
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So if i used a JSON Object with this JSON value:

[{"api":{"results":114,"fixtures":{"17692":{
"fixture_id":"17692","event_timestamp":"1471096800","event_date":"2016-08-13T14:00:00+00:00","league_id":"56","round":"Premier League - 1","homeTeam_id":"52","awayTeam_id":"60",

and i wanted the hometeamID for example would it be something like this?



i ahve the problem understanding which values go into String pageName (results and api in this example, i feel they are wrong)
 
luke davis
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My bad, i've realised i dont even need the PageName for my example. But does the rest of the code look like the correct implementation?

And how from this format would you recommend inserting it to a database?
 
Sheriff
Posts: 24290
55
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

luke davis wrote:And how from this format would you recommend inserting it to a database?



No, don't complicate things like that. You need two steps:

1. Convert the JSON into Java objects.

2. Insert those objects into the database.
 
Now I am super curious what sports would be like if we allowed drugs and tiny ads.
global solutions you can do at home or in your backyard
https://www.kickstarter.com/projects/paulwheaton/better-world-boo
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!