Main content:
1.JDBC connection database (edit) steps (there are mainly six steps)
1. Register the driver: Class.forName("com.mysql.jdbc.Driver"); displayed load into the JVM
2. Get the connection: (1) param1: The url to connect to the database-----》 String url="jdbc:mysql://localhost:3306/test?"+ "useUnicode=true&characterEncoding=UTF8";// Prevent garbled code
param2: Username to connect to the database--》 String user="h4";
param3: Password to connect to the database----》 String pass="111";
Connection conn=DriverManager.getConnection(url,user,pass); //Methods under DriverManager: getConnection(String url,String username,String password)
(2) Next we analyze the url: "jdbc (this protocol starts with jdbc): mysql (this is a sub-protocol, database management system name): //localhost (database source address): 3306 (target port)/test (table table to be queried)?"
"useUnicode=true&characterEncoding=UTF8"; This is added to prevent garbled code, and it is specified to use Unicode character sets and edit them using UTF-8.
3. Create a Statement statement object (three main methods): Statement stmt=conn.createStatement();//Methods under the Connection interface: Statement createStatement()
PreparedStatement pstmt = conn.PreparedStatement();
CallableStatement cstmt = conn.prepareCall("{CALL demoSp(? , ?)}") ;
Let's analyze them below: (1) The difference between Statement and PreparedStatement objects, which can dynamically set query parameters
(2) Method for setting parameters PreparedStatement.setXXXX(parameterIndex,value). If the database parameter type is varchar, setString is used. If the parameter type is Integer, setInt is used.
(3)CallableStatement.setXXXX(parameterIndex,value) //Set value in the order of parameters
CallableStatement.setXXXX(parameterName,value) //Set value according to the name of the parameter. This name is the name of the formal parameter when defining the stored procedure.
(4) CallableStatement.registerOutParameter method is used to declare a parameter of the output type of a stored procedure to receive the output value of the stored procedure
4. Execute SQL statement: ResultSet rs=stmt.executeQuery(Sql); except for the query statement, executeQuery(); everything else is executedUpdate();
Methods under Statement interface:
boolean execute(String sql): execute SQL statement, true if the return value is the result set, otherwise false
ResultSet executeQuery(String sql): execute SQL statement, return value is ResultSet
int executeUpdate(String sql): execute SQL statement, return value is the number of rows affected
5. Process result set: The getXxxx method of the ResultSet object depends on the type of the field in the table in the database. For example: varchar2 corresponds to getString, if it is integer, the corresponding method is getInt/getLong
While(rs.next()){ rs.getInt(columnIndex); //Get the value of the field through the column number rs.getString(columnName); //Get the value of the field through the column name Common methods under the ResultSet interface: beforeFirst(); Move the cursor to the first record (front of) in the ResultSet afterLast(); Move the cursor to the last record (after) in the ResultSet) absolute(intcolumn): Move the cursor to the specified row relative to the first row, and negative number is relative to the last record previous(): Move the cursor up one row next(): Move the cursor down one row ResultSet.TYPE_SCROLL_SENSITIVE The result set can be scrolled ResultSet.CONCUR_READ_ONLY The result set is read-only and cannot be modified.6. Close the resource: After the operation is completed, you must close jdbc to release the jdbc resources. But the order should be the opposite of when you define the object, just like closing the door, closing it from the inside first and closing it outward.
The following example:
if(rs !=null){//1. Close the result set try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){//2. Close the declared object try{ stmt.close(); } catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){//3. Close the connection (remember to close the previous 1.2 first. Then close the connection) try{ conn.close(); } catch(Exception e){ e.printStackTrace(); } }2. Below is a simple (detailed) query data example
package javacore1;//This is a package name you built. import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet; //The five on the left are the package names you want to import when writing a program. Remember to be packages under java.sql. import java.sql.SQLException;import java.sql.Statement;public class jdbc { //Define a class public static void main(String[] args){ //Main method try{ String driver="com.mysql.jdbc.Driver"; //1. Define the driver name driver as driver as com.mysql.jdbc.Driver String url="jdbc:mysql://localhost:3306/test?" //2. Define url; jdbc is the protocol; mysql is the sub-protocol: represents the database system management name; localhost: 3306 is the address and target port of your database source; test is the location of the table I built myself, and you use yours as the standard. + "useUnicode=true&characterEncoding=UTF8"; //Prevent garbled code; useUnicode=true means using Unicode character set; characterEncoding=UTF8 means using UTF-8 for editing. String user="h4"; //3. Define the user name and write the user you want to connect to. String pass="111"; //4. User password. String querySql="select * from employees"; //5. The table name you want to look for. Class.forName(driver); //6. Register the driver and use Class.froName() in the class class below the java.lang package; the driver here is the driver defined in 1, or Class.forName("com.mysql.jdbc.Driver"); Connection conn=DriverManager.getConnection(url,user,pass); //7. Get the database connection and use the getConnectin(String url, String username,String password) of DriverManager in java.sql to complete // The url, user,pass in brackets are the 2, 3, and 4 steps defined above; Statement stmt=conn.createStatement(); //8. Construct a statement object to execute sql statement: there are mainly three instances of Statement, PreparedStatement, and CallableStatement to implement // The three implementation methods are: Statement stmt = con.createStatement(); // PreparedStatement pstmt = conn.prepareStatement(sql) ; // CallableStatement cstmt = conn.prepareCall("{CALL demoSp(? , ?)}"); ResultSet rs=stmt.executeQuery(querySql); //9. Execute sql and return it to the end; ResultSet executeQuery(String sqlString): used to return a result set (ResultSet) object. while(rs.next()){ //10.Transfer the result set System.out.println("personnel number:"+rs.getString("employee_id")+"salary:"+rs.getString("salary")+"name:"+rs.getString("last_name"));//Use the getString() method to get the data name in your table} if(rs !=null){//11.Close the record set try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){//12.Close the declared object try{ stmt.close(); } catch(SQLException e){ e.printStackTrace(); } } if(stmt !=null){//12.Close the declared object try{ stmt.close(); } catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){//13. Close the connection (remember to close the previous 11.12 first. Then close the connection, just like closing the door, close the inside first, and finally close the outermost one) try{ conn.close(); } catch(SQLException e){ e.printStackTrace(); } } } catch(Exception e){ e.printStackTrace(); } } } }3. To be more intuitive, we encapsulate the acquisition of database connections and release connections in the method to facilitate better operation later.
package javacore1;import java.sql.Connection;import java.sql.DriverManager;public class jdbc{ public static void main(String[] args){ Connection conn= getConnection("h4", "111"); //Get database connection/*,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release the database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }4. Implement eleven functions of processing database information such as query, insertion, deletion, and update.
1. Query data
package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Query { public static void main(String[] args) { Connection conn = getConnection("h4", "111");// Get database connection query(conn); // Method name calls database connection releaseConnection(conn);// Release database connection}// Query data, define query method public static void query(Connection conn){ String Sql="select * from employees"; try{ Statement stmt=conn.createStatement(); //You can also use PreparedStatement to do ResultSet rs=stmt.executeQuery(Sql);//Execute the sql statement and return the end while(rs.next()){//Transfer the result set and go to the next line System.out.println("personnel number:"+rs.getString("employee_id")+"salary:"+rs.getString("salary")); } if(rs !=null){ try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){ try{ stmt.close(); } catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){ try{ conn.close(); } catch(SQLException e){ e.printStackTrace(); } } } catch(Exception e){ e.printStackTrace(); } }//Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } } //Query the data, and the results are displayed in reverse order, reverse order package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class Query { public static void main(String[] args) { Connection conn = getConnection("h4", "111");// Get database connection query(conn); //The method name calls the database connection releaseConnection(conn);//Release the database connection}//Query the data, define the query method public static void query(Connection conn){ String Sql="select * from employees"; try{ Statement stmt=conn.createStatement(); //You can also use PreparedStatement to do ResultSet rs=stmt.executeQuery(Sql);//Execute the sql statement and return the end rs.afterLast(); //Skip to the last line first while(rs.previous()){//Transfer the result set and up the line System.out.println("personnel number:"+rs.getString("employee_id")+"salary:"+rs.getString("salary")); } if(rs !=null){ try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){ try{ stmt.close(); } catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){ try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } } } catch(Exception e){ e.printStackTrace(); } }//Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }2. Insert data
package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Insert { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection insert(conn); // Method name calls database connection releaseConnection(conn); // Release database connection}// Insert data public static void insert(Connection conn) { try { String sql = "insert into employees(employee_id,last_name,salary,department_id,userid)" + " values ('100010', 'xiaogou', '7000','004','9')"; // SQL statement for inserting data Statement stmt1 =conn.createStatement(); // Create Statement object for executing static SQL statement int count = stmt1.executeUpdate(sql); // SQL statement for performing insertion operation and return the number of inserted data System.out.println("Inserted" + count + " data"); //Output the result of the insertion operation conn.close(); //Close the database connection} catch (SQLException e) { e.printStackTrace(); } } //Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (Load) the driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release the database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}3. Delete data
package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Delete { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection delete(conn); // Method name calls database connection; releaseConnection(conn); // Release database connection}// Delete data public static void delete(Connection conn){ String Sql = "delete from employees where employee_id=100009"; try { Statement stmt = conn.createStatement();// Or use the PreparedStatement method stmt.executeUpdate(Sql);// Execute the sql statement if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } } }// Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release the database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}4. Update data
package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Update { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection update(conn); // Method name calls database connection releaseConnection(conn);// Release database connection}// Update data public static void update(Connection conn){ String Sql = "update employees set salary=8000 where employee_id=100005"; try { Statement stmt1 = conn.createStatement();//Or use PreparedStatement method stmt1.executeUpdate(Sql);//Execute the sql statement if (stmt1 != null) { try { stmt1.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } }//Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release the database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}5. Transfer (transactions)
JDBC handles transactions through automatic committing of closing connections:
Connection.setAutoCommit(false);
Submit transaction:
Connection.commit();
Rollback transaction rollback part:
Connection.rollback(Savepoint);
Roll back all:
Connection.rollback();
package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Statement;public class TransCash { public static void main(String[] args) { Connection conn = getConnection("h3", "111111");// Get the database connection transCash(conn); // Method name calls the database connection releaseConnection(conn);// Release the database connection}// Transfer (data conversion) (The original data was 100 and 500, but after implementing this function, it becomes 500 and 100. In fact, to put it bluntly, it is to update the data and change the data) public static void transCash(Connection conn){ Statement stmt = null; try{ conn.setAutoCommit(false);//Close automatic submission String sql = "update employees set salary=500 where employee_id=100001"; stmt = conn.createStatement(); stmt.executeUpdate(sql); sql = "update employees set salary=100 where employee_id=100002"; stmt.executeUpdate(sql);//Execute the sql statement, the above two salaries will be exchanged //int i=1/0; If so, the task will not be completed, because this is a complete thing, and if there is a little failure, it will all fail conn.commit();//Commit transaction}catch(Exception e){ e.printStackTrace(); } finally{ try{ if(stmt != null){ stmt.close(); }catch(Exception e){ e.printStackTrace(); } } }//Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}6. Find stored procedures without parameters
package javacore1;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class ExecProc { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection execProc(conn);// Method name calls database connection releaseConnection(conn);// Release database connection}// Call unargumented stored procedure; public static void execProc(Connection conn){ String sql = "{call raisesalary}"; try { CallableStatement cstmt = conn.prepareCall(sql); cstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } }//Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }7. Find stored procedures with parameters
package javacore1;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.Types;public class GetMulti { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection int cnt = getMulti(conn); // Find stored procedures; System.out.println("People number: " + cnt); releaseConnection(conn); // Release database connection}//The search process has parameters public static int getMulti(Connection conn) { int dept_id = 100001; int cnt = 0; String sql = "{call calc_emp_count(?,?)}"; try { CallableStatement cstmt = conn.prepareCall(sql);//Initialize the Statement object cstmt.setInt(1, dept_id);//CallableStatement.setxxx(parameter, value) or (number, value), and PreparedStatement.setxxx(number, value) can only be like this cstmt.setInt(2, cnt); // cstmt.registerOutParameter(2, Types.INTEGER);//Declare the output parameter cstmt.executeUpdate();//Execute the sql statement cnt = cstmt.getInt(2);//Get the result if (cstmt != null) { cstmt.close();//Release the Statement object} } catch (Exception e) { e.printStackTrace(); } return cnt; }//Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}8. Ordinary processing
package javacore1;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;public class PlanInsert { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection planInsert(conn); // Method name calls database connection releaseConnection(conn); // Release database connection}// Ordinary processing of public static void planInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // SQL statement to insert data PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.executeUpdate(); } System.out.println("Total time taken: "+(System.currentTimeMillis() - startTime)); pstmt.close(); //Close the database connection} catch (SQLException e) { e.printStackTrace(); } } //Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}9. Batch processing
Obtain the original JDBC transaction pattern:
boolean currentTransactionModle = con.getAutoCommit();
Set to transaction mode (off automatic commit):
con.setAutoCommit(false);
Statement stm = con.createStatement();
Three heterogeneous SQL statements:
String sql1 = "delete from user where id = 8";
String sql2 = "update user set name='java' where id = 7";
String sql3 = "insert into user(name,password) values('jdbc','jdbc')";
Add to the Statement's batch processing buffer:
stm.addBatch(sql1);
stm.addBatch(sql2);
stm.addBatch(sql3);
Perform batch updates:
stm.executeBatch();
Submit transactions for this batch update:
con.commit();
Reply to the original transaction mode:
con.setAutoCommit(currentTransactionModle);
package javacore1;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;public class BatchInsert { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection batchInsert(conn); // Method name calls database connection releaseConnection(conn); // Release database connection}//The speed of batch insertion is faster than that of ordinary processing public static void batchInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // The sql statement that inserts data PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.addBatch();//Add to batch processing} int[] result=pstmt.executeBatch(); System.out.println("Total time taken:"+(System.currentTimeMillis() - startTime)); pstmt.close(); //Close the database connection} catch (SQLException e) { e.printStackTrace(); } } //Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}10. Pagination display
package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class Paging { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection paging(conn,1,3);// Method name calls database connection, and define which row to display to which row releaseConnection(conn);// Release the database connection}//Page query public static void paging(Connection conn,int startIndex,int total){ try{ String sql="select * from employees limit ?,?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1, startIndex); pstmt.setInt(2, total); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ System.out.print("Work number: "+rs.getInt(1)); System.out.println("Department number: "+rs.getInt("department_id")); }rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } }//Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}11. The result set can be scrolled
package javacore1;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class ScrpllResult { public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection scrpllResult(conn);// Method name calls database connection releaseConnection(conn);// Release the database connection}//Scroll the result set to display public static void scrollResult(Connection conn){ try{ String sql="select * from employees"; //Scroll can be scrolled//Concurrency, the result set is read-only, and the PreparedStatement can not be modified. PreparedStatement pstmt=conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ //Travel System.out.print("Work number: "+rs.getLong(1)); System.out.println("Name"+rs.getString("last_name")); }while(rs.previous()){//Travel System.out.print("Work number: "+rs.getLong(1)); System.out.println("Work number: "+rs.getLong(1)); } rs.absolute(6);//Travel directly to the line if(rs.next()){ System.out.print("Work number: "+rs.getLong(1)); System.out.println(".........Department number: "+rs.getString("department_id")); } rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } } //Database connection public static Connection getConnection(String user, String pass) { Connection conn = null;//Declare the connection object String driver = "com.mysql.jdbc.Driver";// Driver class name String url = "jdbc:mysql://localhost:3306/test?" // Database URL + "useUnicode=true&characterEncoding=UTF8";// Prevent garbled try { Class.forName(driver);// Register (load) driver conn = DriverManager.getConnection(url, user, pass);// Get database connection} catch (Exception e) { e.printStackTrace(); } return conn; }// Release database connection public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } }}5. Putting the eleven above together is of course the most convenient and easy to save time. (Who need to use, just open the previous comment)
package javacore1;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.sql.Statement;import java.sql.Types;public class jdbcMySql {public static void main(String[] args) { Connection conn = getConnection("h3", "111111"); // Get database connection //query(conn); //1. Query data //insert(conn); //2. Insert data //delete(conn); //3. Delete data //update(conn); //4. Update data //transCash(conn); //5. Transfer//execProc(conn); //6. Find stored procedures without parameters //int cnt = getMulti(conn); //7. Find stored procedures with parameters //System.out.println("People number: " + cnt); //Find stored procedures with parameters //planInsert(conn); //8. Ordinary processing //batchInsert(conn); //9. Batch processing // paging(conn,1,3);//10. Pagination display//scrpllResult(conn);//11. The result set can be scrolled to releaseConnection(conn);// Release database connection}//1. Query data, define query method public static void query(Connection conn){ String Sql="select * from employees"; try{ Statement stmt=conn.createStatement(); // You can also use PreparedStatement to do ResultSet rs=stmt.executeQuery(Sql);//执行sql语句并返还结束while(rs.next()){//遍历结果集System.out.println("人员编号:"+rs.getString("employee_id")+"工资:"+rs.getString("salary")); } if(rs !=null){ try{ rs.close(); } catch (SQLException e){ e.printStackTrace(); } } if(stmt !=null){ try{ stmt.close(); }catch(SQLException e){ e.printStackTrace(); } } if(conn !=null){ try{ conn.close(); }catch(SQLException e){ e.printStackTrace(); } } }catch(Exception e){ e.printStackTrace(); }}//2.插入数据public static void insert(Connection conn) { try { String sql = "insert into employees(employee_id,last_name,salary,department_id,userid)" + " values ('100010', 'xiaogou', '7000','004','9')"; // 插入数据的sql语句Statement stmt1 =conn.createStatement(); // 创建用于执行静态sql语句的Statement对象int count = stmt1.executeUpdate(sql); // 执行插入操作的sql语句，并返回插入数据的个数System.out.println("向biao中插入了" + count + " 条数据"); //输出插入操作的处理结果conn.close(); //关闭数据库连接} catch (SQLException e) { e.printStackTrace(); } }//3.删除数据public static void delete(Connection conn){ String Sql = "delete from employees where employee_id=100009"; try { Statement stmt = conn.createStatement();// 或者用PreparedStatement方法stmt.executeUpdate(Sql);//执行sql语句if (stmt != null) { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } }//4.更新数据public static void update(Connection conn){ String Sql = "update employees set salary=8000 where employee_id=100005"; try { Statement stmt1 = conn.createStatement();//或者用PreparedStatement方法stmt1.executeUpdate(Sql);//执行sql语句if (stmt1 != null) { try { stmt1.close(); } catch (SQLException e) { e.printStackTrace(); } } } catch (SQLException e) { e.printStackTrace(); } } //5.转账(数据调换)(原来数据为100和500，实现这个功能后变成500和100，其实说白了就是更新数据，改数据) public static void transCash(Connection conn){ Statement stmt = null; try{ conn.setAutoCommit(false);//关闭自动提交String sql = "update employees set salary=500 where employee_id=100001"; stmt = conn.createStatement(); stmt.executeUpdate(sql); sql = "update employees set salary=100 where employee_id=100002"; stmt.executeUpdate(sql);//执行sql语句，上面的两个工资将会调换conn.commit();//提交事务}catch(Exception e){ e.printStackTrace(); }finally{ try{ if(stmt != null)stmt.close(); }catch(Exception e){ e.printStackTrace(); } } }//6.调用无参存储过程； public static void execProc(Connection conn){ String sql = "{call raisesalary}"; try { CallableStatement cstmt = conn.prepareCall(sql); cstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } //7.查找存储过程有参public static int getMulti(Connection conn) { int dept_id = 100001; int cnt = 0; String sql = "{call calc_emp_count(?,?)}"; try { CallableStatement cstmt = conn.prepareCall(sql);//初始化Statement对象cstmt.setInt(1, dept_id);//CallableStatement.setxxx(参数，值)或者(数字,值),而PreparedStatement.setxxx(数字,值)只能这样cstmt.setInt(2, cnt); // cstmt.registerOutParameter(2, Types.INTEGER);//声明输出参数cstmt.executeUpdate();//执行sql语句cnt = cstmt.getInt(2);//获取结果if (cstmt != null) { cstmt.close();// 释放Statement对象} } catch (Exception e) { e.printStackTrace(); } return cnt; }//8.普通处理public static void planInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // 插入数据的sql语句PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.executeUpdate(); } System.out.println("总共耗时："+(System.currentTimeMillis() - startTime)); pstmt.close(); //关闭数据库连接} catch (SQLException e) { e.printStackTrace(); } } //9.批量插入的速度要比普通处理的速度快public static void batchInsert(Connection conn) { try { String sql = "insert into test_user1(userid,username,loadingtime)" + " values (?,?,?)"; // 插入数据的sql语句PreparedStatement pstmt=conn.prepareStatement(sql); long startTime=System.currentTimeMillis(); for(int i=0;i<1000;i++){ pstmt.setLong(1, i); pstmt.setString(2, "user"+i); pstmt.setDate(3, new Date(System.currentTimeMillis())); pstmt.addBatch();//添加到批量处理} int[] result=pstmt.executeBatch(); System.out.println("总共耗时："+(System.currentTimeMillis() - startTime)); pstmt.close(); //关闭数据库连接} catch (SQLException e) { e.printStackTrace(); } }//10.分页查询public static void paging(Connection conn,int startIndex,int total){ try{ String sql="select * from employees limit ?,?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1, startIndex); pstmt.setInt(2, total); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ System.out.print("工号："+rs.getInt(1)); System.out.println("部门编号："+rs.getInt("department_id")); }rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } }//11.结果集滚动显示public static void scrpllResult(Connection conn){ try{ String sql="select * from employees"; //结果集可以滚动//并发性,结果集只读，不可以修改PreparedStatement pstmt=conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs=pstmt.executeQuery(); while(rs.next()){ //向下一行遍历System.out.print("工号："+rs.getLong(1)); System.out.println("名字"+rs.getString("last_name")); }while(rs.previous()){//向上一行遍历System.out.print("工号："+rs.getLong(1)); System.out.println("工资"+rs.getInt("salary")); } rs.absolute(6);//表示直接跳到第几行if(rs.next()){ System.out.print("工号："+rs.getLong(1)); System.out.println("..........部门编号："+rs.getString("department_id")); } rs.close(); pstmt.close(); }catch(SQLException e){ e.printStackTrace(); } } //数据库连接public static Connection getConnection(String user, String pass) { Connection conn = null;//声明连接对象String driver = "com.mysql.jdbc.Driver";// 驱动程序类名String url = "jdbc:mysql://localhost:3306/test?" // 数据库URL + "useUnicode=true&characterEncoding=UTF8";// 防止乱码try { Class.forName(driver);// 注册(加载)驱动程序conn = DriverManager.getConnection(url, user, pass);// 获取数据库连接} catch (Exception e) { e.printStackTrace(); } return conn; }//释放数据库连接public static void releaseConnection(Connection conn) { try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } }六.安装下载的数据库驱动程序jar包,不同的数据库需要不同的驱动程序（但是安装方法都是一样的）
在使用JDBC编程时需要连接数据库，导入JAR包是必须的，导入其它的jar包方法同样如此，导入的方法是
打开eclipse
1.右击要导入jar包的项目，点properties
2.左边选择java build path,右边选择libraries
3.选择add External jars
4.选择jar包的按照路径下的确定后就行了。
Java连接MySQL的最新驱动包下载地址
http://www.mysql.com/downloads/connector/j
1.鼠标放在你建的根目录上面。右击，然后选择最下面的properties。
2.然后左边选择java build path,右边选择libraries ，在选择右边的add External jars ，选择jar包的路径，点击确定就可以了
3.装好后，图如下出现你要添加的包。