Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
Win a copy of Java Database Connections & Transactions (e-book only) this week in the JDBC forum!

luke davis

Greenhorn
+ Follow
since Feb 09, 2019
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by luke davis

Would this be the correct way to split the List up by line?

i want to place them into an insert statement but i imagine the easiest way to do this would be to assign them to variables.



i want as


and then to somehow insert these variables into my database (with a prepared statement?). Because im using an arrayList i find it very confusing. I think i have to split each line up and then assign through a loop would you not think?
The current format of my list is:




whereby each separated value by space is a row in my database table. I want to insert the 5 separate values per loop. I want to insert all these lines into my database but am not sure on exactly how. This is also a working connection to my database as of now.

This is my rough idea:




I am using Java and not android.
Any help is amazing.

Many thanks
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?
4 months ago
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)
4 months ago
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 :



4 months ago
I've tried converting it into an array with:





But i get given the error java.lang.ArrayStoreException
4 months ago
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
);
4 months ago
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?
4 months ago
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
4 months ago
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.
4 months ago
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
4 months ago