Java Tutorial/Database/Statement

Материал из Java эксперт
Перейти к: навигация, поиск

Change the fetch size on the result set

   <source lang="java">

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);
 }

}</source>





Create a result set

   <source lang="java">

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);
 }

}</source>





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

   <source lang="java">

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);
 }

}</source>





Deleting a Database Table called my_table from a database.

   <source lang="java">

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");
 }

}</source>





Deleting a Row from a Database Table

   <source lang="java">

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();
 }

}</source>





Get the fetch size of a statement

   <source lang="java">

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);
 }

}</source>





Getting Column Names from a database table in Java

   <source lang="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);
   }
 }

}</source>





Inserting a Row into a Database Table

   <source lang="java">

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);
 }

}</source>





Inserting a Row into a Database Table Using a Prepared Statement

   <source lang="java">

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();
   }
 }

}</source>





Retrieve a rowcount from a ResultSet

   <source lang="java">

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();
 }

}</source>





Retrieving All Rows from a Database Table

   <source lang="java">

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();
 }

}</source>





Set the fetch size on the statement

   <source lang="java">

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);
 }

}</source>





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

   <source lang="java">

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);
 }

}</source>





Statement Batch Update

   <source lang="java">

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!");
 }

}</source>


Источник — «http://jexp.ru/index.php?title=Java_Tutorial/Database/Statement&oldid=4830»