JDBC operation steps and Instances

JDBC operation steps and Instances

1. 
   
   Reprinted please indicate the source, thank you http://blog.csdn.net/harryweasley/article/details/45689023
   
   1. 
      
   2. • Create a program to connect to the database using JDBC, which consists of seven steps:
   1. 1. Load the JDBC driver:
   1. Before connecting to the database, load the driver of the database to be connected to JVM (java Virtual Machine), which is achieved through the static method forName (String className) of java. lang. Class.
   2. For example:

   3. Try {// load the MySql Driver Class. forName ("com. mysql. jdbc. driver ");} catch (ClassNotFoundException e) {System. out. println ("The driver class cannot be found. Failed to load the driver! "); E. printStackTrace ();}

      
      
   4. After the Driver class is loaded, the Driver class instance is registered to the DriverManager class.
   5. 
      
   6. Drivers of different database vendors have different names.
      Oracle10g: oracle. jdbc. driver. OracleDriver
      MySQL5: com. mysql. jdbc. Driver
      SQLServer2005: com. microsoft. sqlserver. jdbc. SQLServerDriver
   1. 2. Provide the URL of the JDBC connection
   1. • The Connection URL defines the protocol, sub-protocol, and data source identification used to connect to the database.
   2. • Writing format: Protocol: Sub-Protocol: data source ID
   3. Protocol: always starts with JDBC in jdbc.
   4. Sub-Protocol: the name of the Bridge Connection driver or database management system.
   5. Data source ID: Mark the address and connection port of the database source.
   6. 
      
   7. Example: (MySql connection URL)

   8.     jdbc:mysql://localhost:3306/test? ;   

   9. 
      Different database products have different connection URLs.
      Oracle10g: jdbc: oracle: thin: @ host name: Port: Database SID
      Jdbc: oracle: thin: @ localhost: 1521: ORCL
      MySQL5: jdbc: mysql: // host name: Port/Database Name
      Jdbc: mysql: // localhost: 3306/test
      SQLServer2005: jdbc: sqlserver: // host name: Port: DatabaseName = Database Name
      Jdbc: sqlserver: // localhost: 1433: DatabaseName = BookDB
      Database username and password
      

1. 3. Create a database connection

1. • To connect to the database, you need to request java. SQL. DriverManager and obtain the Connection object, which represents the Connection of a database.
2. • Use the getConnectin (String url, String username, String password) method of DriverManager to pass in the path of the database to be connected, and obtain the username and password of the database.
3. For example:

4. // Connect to the MySql database. The username and password are both root String url = "jdbc: mysql: // localhost: 3306/test"; String username = "root "; string password = "root"; try {Connection con = DriverManager. getConnection (url, username, password);} catch (SQLException se) {System. out. println ("database connection failed! "); Se. printStackTrace ();}

   
   

1. 4. Create a Statement

1. • To execute SQL statements, you must obtain the java. SQL. Statement instance. The Statement instance can be divided into the following types:
2. 1. Execute static SQL statements. It is usually implemented through a Statement instance.
3. 2. Execute dynamic SQL statements. It is usually implemented through the PreparedStatement instance.
4. 3. Execute the database stored procedure. It is usually implemented through the CallableStatement instance.
5. Specific implementation methods:

6.    Statement stmt = con.createStatement() ;          PreparedStatement pstmt = con.prepareStatement(sql) ;          CallableStatement cstmt =                                con.prepareCall("{CALL demoSp(? , ?)}") ;   

   
   

1. 5. Execute SQL statements

1. The Statement interface provides three methods for executing SQL statements: executeQuery, executeUpdate, and execute.
2. 1. ResultSet executeQuery (String sqlString): executes the SQL statement used to query the database and returns a result set object.
3. 2. int executeUpdate (String sqlString): used to execute INSERT, UPDATE, or DELETE statements and SQL DDL statements, such as CREATE TABLE and DROP TABLE.
4. 3. execute (sqlString): it is used to execute statements that return multiple result sets, multiple update counts, or a combination of the two.
5. Specific implementation code:

6.  ResultSet rs = stmt.executeQuery("SELECT * FROM ...") ;       int rows = stmt.executeUpdate("INSERT INTO ...") ;       boolean flag = stmt.execute(String sql) ;   

   
   

1. 6. processing result

1. Two cases:
2. 1. The number of records affected by this operation is returned when an update is executed.
3. 2. The result returned by executing the query is a ResultSet object.
4. • The ResultSet contains all rows that meet the conditions in the SQL statement, and provides
5. The data in the row.
6. • Use the access method of the result set object to obtain data:

7. While (rs. next () {String name = rs. getString ("name"); String pass = rs. getString (1); // This method is more efficient}

   
   
8. (Columns are numbered from left to right and start from column 1)

1. 7. Disable JDBC objects

1. After the operation is complete, you need to close all the used JDBC objects to release the JDBC resources. The order of closure is the opposite of the declared order:
2. 1. Disable record set
3. 2. Close the statement
4. 3. Close the connection object

If (rs! = Null) {// close record set try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (stmt! = Null) {// close the Declaration try {stmt. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (conn! = Null) {// close the connection object try {conn. close () ;}catch (SQLException e) {e. printStackTrace ();}}

8. Example: JdbcUtils

I encapsulated them into a class and tested them. The code is explained in detail and I will not explain it again.

Package com. jdbc. dbutils; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. util. arrayList; import java. util. hashMap; import java. util. list; import java. util. map; public class JdbcUtils {/*** database username */private final String USER = "root";/*** Database Password */private fin Al String PASSWORD = "123456";/*** DRIVER information of the database */private final String DRIVER = "com. mysql. jdbc. driver ";/*** database access address */private final String URL =" jdbc: mysql: // localhost: 3306/test "; /*** database Connection */connection Connection;/*** SQL statement execution object */PreparedStatement preparedStatement;/*** query returned result set */ResultSet resultSet; public JdbcUtils () {try {Class. forName (DRIVER); System. out. println ("successfully registered driver");} catch (Class NotFoundException e) {e. printStackTrace () ;}/ ** connect to database ** @ return database Connection object */public connection getConnection () {try {Connection = DriverManager. getConnection (URL, USER, PASSWORD);} catch (SQLException e) {e. printStackTrace ();} return connection ;} /*** add, delete, and modify a database table ** @ param SQL * @ param params * @ return * @ throws SQLException */public boolean updateByPrepareStatement (String SQL, list <Obje Ct> params) throws SQLException {boolean flag = false; int result =-1; // indicates the number of rows in the affected database when the user adds, deletes, and modifies the database. // create a PreparedStatement object to send parameterized SQL statements to the database. PreparedStatement = connection. prepareStatement (SQL); int index = 1; if (params! = Null &&! Params. isEmpty () {for (Object object: params) {// use the given Object to set the value of the specified parameter. Index preparedStatement starts from 1. setObject (index ++, object) ;}// execute an SQL statement in this PreparedStatement object. This statement must be an SQL data operation language statement, for example, the // INSERT, UPDATE, or DELETE statement result = preparedStatement.exe cuteUpdate (); flag = result> 0? True: false; return flag;}/*** query returns a single record ** @ param SQL * @ param params * @ return * @ throws SQLException */public Map <String, object> findSimpleResult (String SQL, List <Object> params) throws SQLException {Map <String, Object> map = new HashMap <String, Object> (); int index = 1; // create a PreparedStatement object to send parameterized SQL statements to the database. PreparedStatement = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (Object object: params) {// use the given Object to set the value of the specified parameter. Index starting from 1: preparedStatement. setObject (index ++, object) ;}// execute an SQL query in this PreparedStatement object and return the ResultSet object generated by this query. ResultSet = preparedStatement.exe cuteQuery (); // gets the column numbers, types, and attributes of the ResultSet object. ResultSetMetaData metaData = resultSet. getMetaData (); int col_len = metaData. getColumnCount (); while (resultSet. next () {for (int I = 0; I <col_len; I ++) {// The subscript of the SQL database column is String col_name = metaData starting from 1. getColumnName (I + 1); Object col_value = resultSet. getObject (col_name); if (col_value = null) {col_value = "";} map. put (col_name, col_value) ;}} return map;} public List <Map <String, Object> findMoreResult (String SQL, List <Object> params) throws SQLException {List <Map <String, Object> list = new ArrayList <Map <String, Object> (); int index = 1; // create a PreparedStatement object to send parameterized SQL statements to the database. PreparedStatement = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (Object object: params) {// use the given Object to set the value of the specified parameter. Index starting from 1: preparedStatement. setObject (index ++, object) ;}// execute an SQL query in this PreparedStatement object and return the ResultSet object generated by this query. ResultSet = preparedStatement.exe cuteQuery (); // gets the column numbers, types, and attributes of the ResultSet object. ResultSetMetaData metaData = resultSet. getMetaData (); int col_len = metaData. getColumnCount (); while (resultSet. next () {Map <String, Object> map = new HashMap <String, Object> (); for (int I = 0; I <col_len; I ++) {// The subscript of the SQL database column is String col_name = metaData starting from 1. getColumnName (I + 1); Object col_value = resultSet. getObject (col_name); if (col_value = null) {col_value = "";} map. put (col_name, col_value);} list. Add (map);} return list;}/*** close database ** @ throws SQLException */public void releaseConn () throws SQLException {if (resultSet! = Null) {resultSet. close ();} if (preparedStatement! = Null) {preparedStatement. close ();} if (connection! = Null) {connection. close () ;}} static JdbcUtils jdbcUtils; public static void main (String [] args) throws SQLException {jdbcUtils = new JdbcUtils (); jdbcUtils. getConnection (); // insert (); // update (); // query (); Morequery ();} private static void query () throws SQLException {String SQL = "select * from userinfo where pwd =? "; List <Object> params = new ArrayList <Object> (); params. add ("123"); Map <String, Object> map = jdbcUtils. findSimpleResult (SQL, params); System. out. println (map);} private static void Morequery () throws SQLException {String SQL = "select * from userinfo"; List <Map <String, Object> map = jdbcUtils. findMoreResult (SQL, null); System. out. println (map);}/*** update database content ** @ throws SQLException */private static vo Id update () throws SQLException {String SQL = "update userinfo set username =? Where id = 2 "; List <Object> params = new ArrayList <Object> (); params. add ("liumr"); jdbcUtils. updateByPrepareStatement (SQL, params);}/*** add data ** @ throws SQLException */private static void insert () throws SQLException {String SQL = "insert userinfo (username, pwd) values (?,?) "; List <Object> params = new ArrayList <Object> (); params. add ("liugx"); params. add ("456"); jdbcUtils. updateByPrepareStatement (SQL, params );}}