Java Tutorial/Database/Statement

Change the fetch size on the result set

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}

Create a result set

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}

Creating a Database Table called my_table with one column, col_string, which holds strings.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    
    // Create table called my_table
    String sql = "CREATE TABLE my_table(col_string VARCHAR(254))";
    stmt.executeUpdate(sql);
  }
}

Deleting a Database Table called my_table from a database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    
    stmt.executeUpdate("DROP TABLE my_table");
  }
}

Deleting a Row from a Database Table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    String sql = "DELETE FROM my_table WHERE col_string="a string"";
    int deleteCount = stmt.executeUpdate(sql);
    sql = "DELETE FROM my_table WHERE col_string=?";
    PreparedStatement pstmt = connection.prepareStatement(sql);
    pstmt.setString(1, "a string");
    deleteCount = pstmt.executeUpdate();
  }
}

Get the fetch size of a statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}

Getting Column Names from a database table in Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driver = "com.mysql.jdbc.Driver";
    String user = "root";
    String pass = "root";
    Class.forName(driver);
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", user, pass);
    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM employee6");
    ResultSetMetaData md = rs.getMetaData();
    int col = md.getColumnCount();
    System.out.println("Number of Column : " + col);
     for (int i = 1; i <= col; i++) {
      String col_name = md.getColumnName(i);
      System.out.println(col_name);
    }
  }
}

Inserting a Row into a Database Table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    String sql = "INSERT INTO my_table (col_string) VALUES("a string")";
    stmt.executeUpdate(sql);
  }
}

Inserting a Row into a Database Table Using a Prepared Statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "INSERT INTO my_table (col_string) VALUES(?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);
    // Insert 10 rows
    for (int i = 0; i < 10; i++) {
      // Set the value
      pstmt.setString(1, "row " + i);
      // Insert the row
      pstmt.executeUpdate();
    }
  }
}

Retrieve a rowcount from a ResultSet

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Connection conn = null;
    Statement s = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
    ResultSet r = s
        .executeQuery("SELECT * FROM employee");
    r.last();
    int count = r.getRow();
    r.beforeFirst();
  }
}

Retrieving All Rows from a Database Table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driver = "com.mysql.jdbc.Driver";
    String user = "root";
    String pass = "root";
    Class.forName(driver).newInstance();
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", user, pass);
    Statement st = con.createStatement();
    ResultSet res = st.executeQuery("SELECT * FROM  emp");
    while (res.next()) {
      int i = res.getInt("ID");
      String s = res.getString("name");
      System.out.println(i + "\t\t" + s);
    }
    con.close();
  }
}

Set the fetch size on the statement

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    // Get the fetch size of a statement
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    // Create a result set
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}

Setting the Number of Rows to Prefetch When Executing a SQL Query

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement ();
    int fetchSize = stmt.getFetchSize();
    // Set the fetch size on the statement
    stmt.setFetchSize(100);
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    // Change the fetch size on the result set
    resultSet.setFetchSize(100);
  }
}

Statement Batch Update

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    con.setAutoCommit(false);
    String table1 = "INSERT emp_sal VALUES("v",1200)";
    String table2 = "DELETE FROM movies WHERE title = "r"";
    Statement st = con.createStatement();
    st.addBatch(table1);
    st.addBatch(table2);
    int count[] = st.executeBatch();
    con.rumit();
    con.close();
    System.out.println("Successfully!");
  }
}