Database: JDBC programming, database jdbc Programming

Database: JDBC programming, database jdbc Programming

JDBC (Java Data Base Connectivity, java database connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. To put it bluntly, it is used to directly call SQL commands.

Main steps:

1. Load the JDBC driver
2. Database Connection
3. Create a Statement and add relevant parameters.
4. Execute SQL statements
5. Process execution results
6. Disable JDBC objects

Several important classes:
(1)
Public class DriverManager extends Object
Manage basic services of a group of JDBC drivers.
The main method is:
Public static Connection getConnection (String url, String user, String password) throws SQLException
Attempts to establish a connection to the given Database URL. DriverManager tries to select an appropriate driver from the registered JDBC driver set.

(2)
Public interface PreparedStatement extends Statement
Indicates the objects of pre-compiled SQL statements. SQL statements are pre-compiled and stored in the PreparedStatement object. This object can then be used to efficiently execute the statement multiple times. Compared with the Statement interface, Statement is an object used to execute a static SQL Statement and return the result it generates.
The main method is:
1. void setObject (int parameterIndex, Object x) throws SQLException
Setting the value of a specified parameter with a given object is actually setting the actual parameter for the placeholder of an SQL statement.

2. intexecuteUpdate () throws SQLException

Execute an SQL statement in this PreparedStatement object. The statement must be a Data Manipulation Language (DML) statement, such as an INSERT, UPDATE, or DELETE statement; or an SQL statement without returned content, such as a DDL statement.
Return Value:
(1) number of rows of SQL data operation language (DML) Statements (2) for SQL statements without returned content, 0 is returned

3. ResultSet executeQuery () throws SQLException
Execute an SQL query in this PreparedStatement object and return the ResultSet object generated by this query.
Return Value:
The ResultSet object that contains the data generated by the query. null is not returned.

(3)
Pay attention to the values returned by executing the executeUpdate () and executeQuery () methods. It is found that executeQuery () returns a result set object.
Public interface ResultSet extends Wrapper
The data table of the database result set. It is usually generated by executing a database query statement.

The main method is:
1. ResultSetMetaData getMetaData () throws SQLException

Obtains the number, type, and attribute of the column of the ResultSet object.
The main method of ResultSetMetaData is int getColumnCount () to return the number of columns in this ResultSet object; String getColumnName (int column) to get the name of the specified column.

2. Object getObject (int columnIndex) throws SQLException

Obtain the value of the column specified in the current row of the ResultSet Object as an Object in Java programming language.

Example 1: MySql + JDBC

For Windows users, visit http://dev.mysql.com/downloads/windows/installer/download the automatic configuration and installation version of mysql.

Use Navicat as a MySql database management tool

In addition, you need to download the JDBC driver http://dev.mysql.com/downloads/connector/j/, and the jar package in the Project License.

① Create a tool class JdbcUtils

Import java. lang. reflect. field; 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 login account password private final String USERNAME = "root"; private final Strin G PASSWORD = "123456"; // jdbc driver private final String DRIVER = "com. mysql. jdbc. driver "; // database address private final String URL =" jdbc: mysql: // localhost: 3306/test "; // The private Connection connection object of the three important classes; private PreparedStatement ps; private ResultSet resultSet; public JdbcUtils () {try {// Step 1: load the driver Class. forName (DRIVER); // Step 2: establish a connection. The processing here is to complete these two steps when the tool class object is instantiated. connection = DriverManager. getConnection (URL, U SERNAME, PASSWORD);} catch (ClassNotFoundException e) {// catch Block e automatically generated by TODO. printStackTrace ();} catch (SQLException e) {// catch Block e automatically generated by TODO. printStackTrace () ;}// the encapsulated update function public int update (String SQL, List <Object> params) throws SQLException {int result = 0; // Step 3: create a Statement and add the related parameter ps = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) // note that the subscript of the database starts from 1, and the I placeholder is filled with the I value of params ps. setObject (I + 1, params. get (I);} // Step 4: Execute the SQL statement. If Steps 5 and 6 are left to the client for processing result = ps.exe cuteUpdate (); return result ;} // The encapsulated query function returns a List set of maps, and the database stores the public List <Map <String, Object> query (String SQL, list <Object> params) throws SQLException {List <Map <String, Object> list = new ArrayList <Map <String, Objec T> (); // Step 3: Create a Statement and add the related parameter ps = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) ps. setObject (I + 1, params. get (I);} // Step 4: Execute the SQL statement resultSet = ps.exe cuteQuery (); // Step 5: processing execution results // obtain the attributes of the columns of this ResultSet object. ResultSetMetaData metaData = resultSet. getMetaData (); // The length of the column int col_len = metaData. getColumnCount (); // if there is a record while (resultSet. next () {// store the record as map <String, Object> Map = new HashMap <String, Object> (); for (int I = 0; I <Col_len; I ++) {// obtain the key value based on the column name and place it in map String col_name = metaData. getColumnName (I + 1); Object col_value = resultSet. getObject (col_name); map. put (col_name, col_value);} // Add the record to list. add (map);} // returns the listreturn list after traversing the resultSet;} // encapsulate step 6 to disable the JDBC object public void release () {if (resultSet! = Null) try {resultSet. close ();} catch (SQLException e) {// catch Block e automatically generated by TODO. printStackTrace ();} if (ps! = Null) try {ps. close ();} catch (SQLException e) {// catch Block e. printStackTrace ();} if (connection! = Null) try {connection. close ();} catch (SQLException e) {// catch Block e. printStackTrace ();}}}

② TestMain class

Import java. SQL. SQLException; import java. util. arrayList; import java. util. list; import java. util. map; public class TestMain {public static void main (String [] args) {// method stub automatically generated by TODO JdbcUtils jdbcUtils = new JdbcUtils ();/* execute 1: create table * // String sql0 = // "create table userinfo (id int primary key auto_increment, username varchar (64), password varchar (64 ))"; // try {// jdbcUtils. update (sql0, null); //} catch (SQLException e) {// catch Block automatically generated by TODO // e. printStackTrace (); //}/* execution 2: Add a record * // String SQL = "insert into userinfo (username, password) values (?,?) "; // List <Object> params = new ArrayList <Object> (); // params. add ("jack"); // params. add ("123456"); // try {// jdbcUtils. update (SQL, params); //} catch (SQLException e) {// catch Block automatically generated by TODO // e. printStackTrace (); //}/* execution 3: modify a record * // String sql1 = "update userinfo set username =? Where id =? "; // List <Object> param1 = new ArrayList <Object> (); // param1.add (" tommy "); // param1.add (2 ); // try {// jdbcUtils. update (sql1, param1); //} catch (SQLException e) {// catch Block automatically generated by TODO // e. printStackTrace (); //}/* execution 4: delete a record * // String sql2 = "delete from userinfo where username = 'Tommy '"; // try {// jdbcUtils. update (sql2, null); //} catch (SQLException e) {// catch Block automatically generated by TODO // e. printStackTrace (); //}/* execution 5: Query all records * // String sql3 = "select * from userinfo "; // try {// List <Map <String, Object> list = jdbcUtils. query (sql3, null); // System. out. println (list); //} catch (SQLException e) {// catch Block automatically generated by TODO // e. printStackTrace ();//}}}

Step 1: Create a new table. (If the execution steps described below are not described, they are all executed separately and commented out in other steps)

Step 2: Create a table with three fields. The id is the primary key.

Perform steps three or two consecutively: Add two records

Perform Step 4: Modify the second record

Step 5: Delete the second record

Step 6: query a record

Perform Step 1 and Step 5 again: Query multiple records

Example 2: Use the reflection mechanism to query database records

① Create a UserInfo class that matches the database fields

public class UserInfo {private int id;private String username;private String password;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getUsername() {return username;}public void setUsername(String username) {this.username = username;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}@Overridepublic String toString() {return "UserInfo [id=" + id + ", username=" + username + ", password="+ password + "]";}}

② Add a database query function that applies the reflection mechanism to the JdbcUtils class

Public <T> List <T> queryRef (String SQL, List <Object> params, Class <T> cls) throws Exception {List <T> list = new ArrayList <> (); ps = connection. prepareStatement (SQL); if (params! = Null &&! Params. isEmpty () {for (int I = 0; I <params. size (); I ++) ps. setObject (I + 1, params. get (I);} resultSet = ps.exe cuteQuery (); ResultSetMetaData metaData = resultSet. getMetaData (); int col_len = metaData. getColumnCount (); while (resultSet. next () {// If a record exists, instantiate an object T record = cls. newInstance (); for (int I = 0; I <col_len; I ++) {String col_name = metaData. getColumnName (I + 1); Object col_value = resultSet. getObject (col_name); // assign the column value to the corresponding Field field Field = cls of the object. getDeclaredField (col_name); field. setAccessible (true); field. set (record, col_value);} // Add this record to List. add (record);} return list ;}

③ TestMain class, add an execution operation

/* Execution 6: reflection mechanism query record */String sqlQuery = "select * from userinfo"; try {List <UserInfo> list = jdbcUtils. queryRef (sqlQuery, null, UserInfo. class); System. out. println (list);} catch (Exception e) {// catch Block e automatically generated by TODO. printStackTrace ();} finally {jdbcUtils. release ();}

Step 6