write to a mysql database

Hello

I was working with the code attached. It is writing to a database but there are two problems.
(1) it seems to want to create a new table and does not write to an existing table.
(2) the current code takes a csv file and writes it to a database. What I need to do is to write the code from line 134 to 138 to the database. The code in these lines create a list of three fields like this (this list has 14 rows and so I would be writing 14 entries to the database) :
ID: 0.0, actual: no, predicted: no
ID: 0.0, actual: no, predicted: no
ID: 1.0, actual: yes, predicted: yes
ID: 2.0, actual: yes, predicted: yes
ID: 2.0, actual: yes, predicted: yes
ID: 2.0, actual: no, predicted: no
ID: 1.0, actual: yes, predicted: yes
ID: 0.0, actual: no, predicted: no
ID: 0.0, actual: yes, predicted: yes
ID: 2.0, actual: yes, predicted: yes
ID: 0.0, actual: yes, predicted: yes
ID: 1.0, actual: yes, predicted: yes
ID: 1.0, actual: yes, predicted: yes
ID: 2.0, actual: no, predicted: no

import weka.core.Instance;
import weka.core.Instances;
import weka.core.converters.ArffLoader;
import weka.classifiers.bayes.NaiveBayesUpdateable;
 //next three are for writing to the database. Probably you don't need the others since you have '*'
 import weka.core.*;
  import weka.core.converters.*;
  import java.io.*;

import java.io.File;


import weka.classifiers.Evaluation;
import weka.classifiers.trees.J48;
import java.io.BufferedReader;
import java.io.FileReader;
import weka.core.converters.ConverterUtils.DataSource;
import weka.core.Instances;
import weka.experiment.InstanceQuery;

/**
* This example trains NaiveBayes incrementally on data obtained
* from the ArffLoader.
*
* @author FracPete (fracpete at waikato dot ac dot nz)
*/
public class JavaWekaJ48TestTrainPredReadWriteSQL { 


 /**
  * Expects an ARFF file as first argument (class attribute is assumed
  * to be the last attribute).
  *weather.nominalTest.arff
  * @param args        the commandline arguments
  * @throws Exception  if something goes wrong
  */
  
  public static void writeToDB(String string){


try{
	
       /*BufferedReader readerTest = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominalTest.arff"));
        Instances dataTest = new Instances(readerTest);
        readerTest.close();
		
			DataSource sourceTest = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTest = sourceTest.getDataSet();	

 
	     //Instances data = new Instances(new BufferedReader(new FileReader(args[0])));
		 */
		 DataSource source = new DataSource(string);
 Instances data = source.getDataSet();	
 
      data.setClassIndex(data.numAttributes() - 1);
 
      DatabaseSaver save = new DatabaseSaver();
      save.setUrl("jdbc:mysql://mysql03.ixwebingrnet");
      save.setUser("C261605");
      save.setPassword("Wri");
      save.setInstances(data);
      save.setRelationForTableName(false);
      save.setTableName("weather");
      save.connectToDatabase();
      save.writeBatch();
   }
catch(Exception ex){

ex.printStackTrace();
}	
	
	}
 public static void main(String[] args) throws Exception {



     /*   BufferedReader readerTrain = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominal.arff"));
        Instances dataTrain = new Instances(readerTrain);
        readerTrain.close();
	
//If csv do it this way:	
		DataSource sourceTrain = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTrain = sourceTrain.getDataSet();
 
 */
 
  InstanceQuery queryTR = new InstanceQuery();
 queryTR.setUsername("C261605");
 queryTR.setPassword("Re");
 queryTR.setQuery("select * from weatherSample");
 // You can declare that your data set is sparse
 // query.setSparseData(true);
 Instances dataTrain = queryTR.retrieveInstances();
		
        /*BufferedReader readerTest = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominalTest.arff"));
        Instances dataTest = new Instances(readerTest);
        readerTest.close();
		
			DataSource sourceTest = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTest = sourceTest.getDataSet();	
*/

  InstanceQuery queryTS = new InstanceQuery();
 queryTS.setUsername("C261605");
 queryTS.setPassword("Re");
 queryTS.setQuery("select * from weatherSample");
 // You can declare that your data set is sparse
 // query.setSparseData(true);
 Instances dataTest = queryTS.retrieveInstances();

 if (dataTrain.classIndex() == -1)
dataTrain.setClassIndex(dataTrain.numAttributes() - 1);
if (dataTest.classIndex() == -1)
dataTest.setClassIndex(dataTest.numAttributes() - 1);


        J48 cls = new J48();
        cls.buildClassifier(dataTrain);




Evaluation eval = new Evaluation(dataTest);
eval.evaluateModel(cls, dataTest);
System.out.println(eval.toSummaryString("\nResults\n======\n", false));
        



 for (int i = 0; i < dataTest.numInstances(); i++) {
   double pred = cls.classifyInstance(dataTest.instance(i));
   System.out.print("ID: " + dataTest.instance(i).value(0));
   System.out.print(", actual: " + dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
   System.out.println(", predicted: " + dataTest.classAttribute().value((int) pred));
        } 


	JavaWekaJ48TestTrainPredReadWriteSQL.writeToDB("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 }
}

Open in new window


Any help on this?
onyourmarkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

for_yanCommented:
You need to learn how to use JDBC and instert into the table.

Very approximately it should be something like below:
You need first to create the table if you want to write all the time to the same table
- if you don't want to create table every time you run this program

import weka.core.Instance;
import weka.core.Instances;
import weka.core.converters.ArffLoader;
import weka.classifiers.bayes.NaiveBayesUpdateable;
 //next three are for writing to the database. Probably you don't need the others since you have '*'
 import weka.core.*;
  import weka.core.converters.*;
  import java.io.*;

import java.io.File;


import weka.classifiers.Evaluation;
import weka.classifiers.trees.J48;
import java.io.BufferedReader;
import java.io.FileReader;
import weka.core.converters.ConverterUtils.DataSource;
import weka.core.Instances;
import weka.experiment.InstanceQuery;

/**
* This example trains NaiveBayes incrementally on data obtained
* from the ArffLoader.
*
* @author FracPete (fracpete at waikato dot ac dot nz)
*/
public class JavaWekaJ48TestTrainPredReadWriteSQL { 


 /**
  * Expects an ARFF file as first argument (class attribute is assumed
  * to be the last attribute).
  *weather.nominalTest.arff
  * @param args        the commandline arguments
  * @throws Exception  if something goes wrong
  */
  
  public static void writeToDB(String string){


try{
	
       /*BufferedReader readerTest = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominalTest.arff"));
        Instances dataTest = new Instances(readerTest);
        readerTest.close();
		
			DataSource sourceTest = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTest = sourceTest.getDataSet();	

 
	     //Instances data = new Instances(new BufferedReader(new FileReader(args[0])));
		 */
		 DataSource source = new DataSource(string);
 Instances data = source.getDataSet();	
 
      data.setClassIndex(data.numAttributes() - 1);
 
      DatabaseSaver save = new DatabaseSaver();
      save.setUrl("jdbc:mysql://mysql03.ixwebingrnet");
      save.setUser("C261605");
      save.setPassword("Wri");
      save.setInstances(data);
      save.setRelationForTableName(false);
      save.setTableName("weather");
      save.connectToDatabase();
      save.writeBatch();
   }
catch(Exception ex){

ex.printStackTrace();
}	
	
	}
 public static void main(String[] args) throws Exception {



     /*   BufferedReader readerTrain = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominal.arff"));
        Instances dataTrain = new Instances(readerTrain);
        readerTrain.close();
	
//If csv do it this way:	
		DataSource sourceTrain = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTrain = sourceTrain.getDataSet();
 
 */
 
  InstanceQuery queryTR = new InstanceQuery();
 queryTR.setUsername("C261605");
 queryTR.setPassword("Re");
 queryTR.setQuery("select * from weatherSample");
 // You can declare that your data set is sparse
 // query.setSparseData(true);
 Instances dataTrain = queryTR.retrieveInstances();
		
        /*BufferedReader readerTest = new BufferedReader(
         new FileReader("C:/Program Files/Weka-3-7/data/weather.nominalTest.arff"));
        Instances dataTest = new Instances(readerTest);
        readerTest.close();
		
			DataSource sourceTest = new DataSource("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 Instances dataTest = sourceTest.getDataSet();	
*/

  InstanceQuery queryTS = new InstanceQuery();
 queryTS.setUsername("C261605");
 queryTS.setPassword("Re");
 queryTS.setQuery("select * from weatherSample");
 // You can declare that your data set is sparse
 // query.setSparseData(true);
 Instances dataTest = queryTS.retrieveInstances();

 if (dataTrain.classIndex() == -1)
dataTrain.setClassIndex(dataTrain.numAttributes() - 1);
if (dataTest.classIndex() == -1)
dataTest.setClassIndex(dataTest.numAttributes() - 1);


        J48 cls = new J48();
        cls.buildClassifier(dataTrain);




Evaluation eval = new Evaluation(dataTest);
eval.evaluateModel(cls, dataTest);
System.out.println(eval.toSummaryString("\nResults\n======\n", false));
        

 String url = "jdbc:mysql://mysql03.ixwebingrnet";
  String driver = "com.mysql.jdbc.Driver";
  String userName = "C261605"; 
  String password = "Wri";
   //save.setUser("C261605");
     // save.setPassword("Wri");

  
  Class.forName(driver).newInstance();
Connection  conn = DriverManager.getConnection(url+dbName,userName,password);

PreparedStatement pst = conn.prepareStatement("insrt into mytable values( ?,?,?)");





 for (int i = 0; i < dataTest.numInstances(); i++) {
   double pred = cls.classifyInstance(dataTest.instance(i));
   System.out.print("ID: " + dataTest.instance(i).value(0));
   pst.setDouble(1,dataTest.instance(i).value(0));
   System.out.print(", actual: " + dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
pst.setString(2, dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
   System.out.println(", predicted: " + dataTest.classAttribute().value((int) pred));

pst.setString(3,dataTest.classAttribute().value((int) pred);
  pst.excute();
        } 

conn.close();

	JavaWekaJ48TestTrainPredReadWriteSQL.writeToDB("C:/Program Files/Weka-3-7/data/weather.nominal.csv");
 }
}

Open in new window

for_yanCommented:
This is the critical part of the above code:

 String url = "jdbc:mysql://mysql03.ixwebingrnet";
  String driver = "com.mysql.jdbc.Driver";
  String userName = "C261605"; 
  String password = "Wri";
   //save.setUser("C261605");
     // save.setPassword("Wri");

  
  Class.forName(driver).newInstance();
Connection  conn = DriverManager.getConnection(url+dbName,userName,password);

PreparedStatement pst = conn.prepareStatement("insrt into mytable values( ?,?,?)");





 for (int i = 0; i < dataTest.numInstances(); i++) {
   double pred = cls.classifyInstance(dataTest.instance(i));
   System.out.print("ID: " + dataTest.instance(i).value(0));
   pst.setDouble(1,dataTest.instance(i).value(0));
   System.out.print(", actual: " + dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
pst.setString(2, dataTest.classAttribute().value((int) dataTest.instance(i).classValue()));
   System.out.println(", predicted: " + dataTest.classAttribute().value((int) pred));

pst.setString(3,dataTest.classAttribute().value((int) pred);
  pst.excute();
        } 

conn.close();

Open in new window

for_yanCommented:
LLook at this example below.
You can either use Statement and direct insert as shown in the code below,
or use PreparedStatement as I showed above - those are basically equivalent things - PreparedStatement mey sometimes be faster

http://www.roseindia.net/jdbc/jdbc-mysql/InsertValues.shtml

import java.sql.*;

public class InsertValues{
 public static void main(String[] args) {
 System.out.println("Inserting values in Mysql database table!");
  Connection con = null;
  String url = "jdbc:mysql://localhost:3306/";
  String db = "jdbctutorial";
  String driver = "com.mysql.jdbc.Driver";
  try{
  Class.forName(driver);
  con = DriverManager.getConnection(url+db,"root","root");
  try{
  Statement st = con.createStatement();
  int val = st.executeUpdate("INSERT employee VALUES("+13+","+"'Aman'"+")");
  System.out.println("1 row affected");
  }
  catch (SQLException s){
  System.out.println("SQL statement is not executed!");
  }
  }
  catch (Exception e){
  e.printStackTrace();
  }
  }
} 

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

for_yanCommented:
If you plan to do this for some time you need to read through this tutorial:

http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html

and understand some basic points about JDBC
onyourmarkAuthor Commented:
Hello. Thanks very much. In your example you wrote:

pst.setDouble(1,dataTest.instance(i).value(0));

I think the 1 refers to the first question mark in
PreparedStatement pst = conn.prepareStatement("insrt into mytable values( ?,?,?)");

I think the 0 in .value(0) refers to the first column of the database.

Also, I guess you were just giving an example because in the data output I wrote there would not be any Doubles right?
I think all pst.set statements would be strings. Is this correct?
for_yanCommented:
All dpends how you cretaed the table
i thought you declared table like that:

create table ( ID number,
actual varchar2(3),
predicted varchar2(3))

so the first field is number adnd the other two fileds are strings (vartchare2 is Orcale name, don't know how they call it in MySQL)

my assumption was that
this entity in your progrma is avalue of type double
dataTest.instance(i).value(0));
I thought this vlaue is string:
dataTest.classAttribute().value((int) dataTest.instance(i).classValue()

and the last value also string


>I think the 1 refers to the first question mark in
>PreparedStatement pst = conn.prepareStatement("insrt into mytable values( ?,?,?)");

that is correct

>I think the 0 in .value(0) refers to the first column of the database.

this is not correct
I just assumed that values 1.0, 2.0, etc. are those which are produced in your code
dataTest.instance(i).value(0)); - because when you printed those were the values

if this expression
dataTest.instance(i).value(0));
is indeed String and the first field in the table is declared as varchar2
then you'll need to us method not setDouble(1,...)
but rather setString(1,...)


>Also, I guess you were just giving an example because in the data output I wrote there would not >be any Doubles right?
>I think all pst.set statements would be strings. Is this correct?

I didn't investigate in detail your code to understand
if
dataTest.instance(i).value(0));
was string or double
Looking at your outputt 1.0, 2.0, etc. I assumed that
thhis experssion produces double value which only in the System.out.println()
method is changed into the string "1.0" which is actually printed.

But you need to understand what type is this value
dataTest.instance(i).value(0));

before you create the actual
pst.setXXX .. method

You also need to design your table with good thought
Think if you want just to print the first column - then you can use string (varcjar)
for this first field, but suppose you will need to add the first column value from one row with another row and you want to add them as numbers.
If this is the possibility, then you need to make sure that yourt fisrt filed of the table is number;
otherwise you can make the table with the first filed as string.
That is always an important decision as the table may then live for yaears and if you design it inconveniently it would be really bad.

So, please read the tutorial and consult API of the Statement, PreapredStatement, classes
These are the things you cannot learn from EE - you need to read and understand the basics of JDBC.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.