Solved

write to a mysql database

Posted on 2012-03-19
6
595 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:onyourmark
  • 5
6 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 37736703
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

0
 
LVL 47

Expert Comment

by:for_yan
ID: 37736706
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

0
 
LVL 47

Expert Comment

by:for_yan
ID: 37736715
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

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:for_yan
ID: 37736721
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
0
 

Author Comment

by:onyourmark
ID: 37741176
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?
0
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 37741366
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.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to dynamically set the form action using jQuery.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now