SQL from Java

From SQLZoo
Jump to navigation Jump to search

You can connect to an SQL engine using JDBC. You will need to obtain a JDBC driver from the appropriate database.

  • The example given is based on the MySQL JDBC Connector/J which may be found at MySQL
  • Extract the MySQL Connector/J into a directory C:\thingies. (If you know what you are doing you will put this in your classpath.)
  • The program connects to mysql server running on my computer - the user name and password is valid - you may use it too.
  • More examples at http://progzoo.net/wiki/Read_From_a_Database

You need to compile and execute the java from the command prompt:

javac CIA.java
java -classpath "C:/thingies/mysql-connector-java-2.0.14;." 
/* CIA.java
   From http://sqlzoo.net By Andrew Cumming
*/
import java.sql.*;
public class CIA{
  public static void main(String[] args){
    Connection myCon;
    Statement myStmt;
    try{
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      // Connect to an instance of mysql with the follow details:
      // machine address: pc236nt.napier.ac.uk
      // database       : gisq
      // user name      : scott
      // password       : tiger
      myCon = DriverManager.getConnection(
              "jdbc:mysql://pc236nt.napier.ac.uk/gisq",
              "scott","tiger");
      myStmt = myCon.createStatement();
      ResultSet result = myStmt.executeQuery(
         "SELECT * FROM cia WHERE population>200000000");
      while (result.next()){
        System.out.println(result.getString("name"));
      }
      myCon.close();
    }
    catch (Exception sqlEx){
      System.err.println(sqlEx);
    }
  }
}
>

It should respond with the names of four countries.

DataWars, Data Science Practice Projects - LogoDataWars: Practice Data Science/Analysis with +100 Real Life Projects