Basic knowledge review Part 3 database access and basic knowledge review Part 3

Basic knowledge review Part 3 database access and basic knowledge review Part 3

This article focuses on database operations. Here it does not apply to hibernate or mybatis. It is explained with the original JDBC. After understanding these principles, it is easier to understand and learn about hibernate or mybatis.

1: simple jdbc operations

First, download the jdbc driver jar package. If you do not want to download it in C: \ Program Files (x86) \ MySQL \ Connector J 5.1.26 can also be found. If it is a maven project, introduce the following dependency package on pom.

1 <dependency> 2 <groupId> mysql </groupId> 3 <artifactId> mysql-connector-java </artifactId> 4 <version> 5.1.26 </version> 5 </dependency>View Code1.1: Configure database connection 1 Class. forName ("com. mysql. jdbc. driver "); // load the database Driver 2 String url =" jdbc: mysql: // localhost: 3306/blog "; // data connection address 3 String username =" root "; // login name 4 String password = "123456"; // password 5 connection = DriverManager. getConnection (url, username, password); // obtain the database connectionView Code1.2: initiate a request to the database (add, delete, modify, and query) 1.2.1: statement

Statement is used to send SQL statement objects based on database connections. Simple SQL statements without parameters are used.

1.2.2: PreparedStatement

Since the SQL statement passed in the program must be pre-compiled, including analysis and optimization, it is inefficient to execute another SQL statement with different parameters, you can use the preparedStatement object to pre-compile SQL statements for a long time. (Recommended)

1.2.3: CallableStatement

This indicates the object that processes the stored procedure.

1.3: Role of ResultSet

This is mainly the result set returned after SQL Execution, and then the relevant results are converted using the next attribute.

1.4: executeUpdate (), executeQuery (), and execute ()

ExecuteUpdate () mainly processes addition, deletion, and modification statements, returns the number of affected rows, and can also create or update databases and create or update table structures.

ExecuteQuery () is mainly used to query statements starting with select.

Execute () indicates a Special execution, which is mainly used to return multiple result sets and update counts.

Paste the code

1 public class JDBCHelper {2 private Connection connection; 3 private PreparedStatement preparedStatement; 4 public JDBCHelper () {5 try {6 Class. forName ("com. mysql. jdbc. driver "); 7 String url =" jdbc: mysql: // localhost: 3306/blog "; 8 String username =" root "; 9 String password =" 123456 "; 10 connection = DriverManager. getConnection (url, username, password); 11} 12 catch (ClassNotFoundException e) {13 e. p RintStackTrace (); 14} catch (SQLException e) {15 e. printStackTrace (); 16} 17} 18/** 19 * Create Database 20 * SQL Statement 21 */22 public void createDatabase (String SQL) 23 {24 statement Statement = null; 25 try {26 statement = connection. createStatement (); // The primary user executes a static SQL statement () without parameters and returns the result 27 statement.exe cuteUpdate (SQL); 28 System. out. println ("database created successfully"); 29} catch (SQLException e) {30 e. printStackTrace (); 31} 32 finally {33 if (statement! = Null) {34 try {35 statement. close (); 36} catch (SQLException e) {37} 38 statement = null; 39} 40} 41} 42/** 43 * add, delete, modify, 44 * @ param SQL 45 * @ param paramters 46 * @ return 47 * @ throws SQLException 48 */49 public int update (String SQL, object... paramters) throws SQLException 50 {51 try {52 preparedStatement = connection. prepareStatement (SQL); 53 for (int I = 0; I <paramters. length; I ++) {54 PreparedStatement. setObject (I + 1, paramters [I]); 55} 56 return preparedStatement.exe cuteUpdate (); 57} 58 catch (SQLException e) 59 {60 e. printStackTrace (); 61} 62 finally {63 if (preparedStatement! = Null) 64 {65 preparedStatement. close (); 66} 67 preparedStatement = null; 68} 69 return 0; 70} 71/** 72 * use and query 73 * @ param SQL 74 * @ param paramters 75 * @ return 76 * @ throws SQLException 77 */78 public ResultSet query (String SQL, object... paramters) throws SQLException 79 {80 try {81 preparedStatement = connection. prepareStatement (SQL); 82 for (int I = 0; I <paramters. length; I ++) {83 prepar EdStatement. setObject (I + 1, paramters [I]); 84} 85 return preparedStatement.exe cuteQuery (); 86} 87 catch (SQLException e) 88 {89 e. printStackTrace (); 90} 91 finally {92 if (preparedStatement! = Null) 93 {94 preparedStatement. close (); 95} 96 preparedStatement = null; 97} 98 return null; 99} 100}JDBCHelper1.4: database transactions

Definition of a transaction: A Program Execution Unit that accesses and may update various data items in the database (in general, a program executes all SQL statements at the same time or fails to roll back)

We should first look at three definitions before learning about transactions.

Dirty read: a transaction is modifying a row of data but is not committed, and another transaction is reading this row of data. This is dirty read.

Repeatable reading: one transaction reads a row of data, and the other transaction modifies the row before the end of the transaction, but the modification is not committed to the database, at this time, when the first transaction reads the data again, two data inconsistencies occur, which means the data cannot be read repeatedly.

Phantom read: when a transaction reads data that meets the conditions, another transaction inserts a data record. When the first transaction reads the data again, it finds that there is one more data record. This is Phantom read.

Because the preceding three conditions exist, the isolation level is displayed. Of course, the more efficient the isolation level is, the lower the isolation level is. The following is the isolation level in section 4.

Connection. TRANSACTION_READ_UNCOMMITTED: At the isolation level, three conditions may occur.

Connection. TRANSACTION_READ_COMMITTED: This isolation level avoids dirty read.

Connection. TRANSACTION_REPEATABLE_READ: This isolation level prevents dirty reads and unrepeatable reads.

Connection. TRANSACTION_SERIALIZABLE: up to three isolation levels can be avoided.

After learning about this situation, we can start transaction processing. By default, transactions are not executed.

The first step is to enable the transaction: connection. setAutoCommit (false); // disable the automatic commit function (true by default)

Then, set the isolation level: connection. setTransactionIsolation (connection. TRANSACTION_REPEATABLE_READ );

OK, with the above, we can perform the transaction and paste the code below for reference only.

1 public void addUserAndRole (UserBean userbean, UserRoleBean userRoleBean) throws SQLException 2 {3 connection. setAutoCommit (false); // disable the automatic submission function 4 connection. setTransactionIsolation (connection. TRANSACTION_REPEATABLE_READ); 5 6 7 String userSql = "insert into B _user (name, password, level, des, tel) values" + 8 "(?,?,?,?,?) "; 9 String roleSql =" insert into B _user_role (userId, roleId) values (?,?) "; 10 try {11 12 preparedStatement = connection. prepareStatement (userSql); 13 preparedStatement. setString (1, userbean. getName (); 14 preparedStatement. setString (2, userbean. getPassword (); 15 preparedStatement. setInt (3, userbean. getLevel (); 16 preparedStatement. setString (4, userbean. getDes (); 17 preparedStatement. setString (5, userbean. getTel (); 18 preparedStatement.exe cuteUpdate (); 19 if (userRoleBean. getRoleId () = 0) {20 throw new Exception (); 21} 22 preparedStatement = connection. prepareStatement (roleSql); 23 preparedStatement. setInt (1, userRoleBean. getUserId (); 24 preparedStatement. setInt (2, userRoleBean. getRoleId (); 25 preparedStatement.exe cute (); 26 connection. commit (); 27 System. out. println ("executed successfully"); 28} catch (Exception e) {29 System. out. println ("failed to execute"); 30 connection. rollback (); 31} 32}Transaction 1 public static void main (String [] args) 2 {3 JDBCHelper jdbcHelper = new JDBCHelper (); 4 UserBean userBean = new UserBean (); 5 userBean. setName ("Zhang San"); 6 userBean. setPassword ("123456"); 7 userBean. setLevel (1); 8 userBean. setDes ("... "); 9 userBean. setTel ("123456"); 10 11 UserRoleBean userRoleBean = new UserRoleBean (); 12 userRoleBean. setRoleId (0); 13 userRoleBean. setUserId (2); 14 15 try {16 jdbcHelper. addUserAndRole (userBean, userRoleBean); 17} catch (SQLException e) {18 // TODO Auto-generated catch block19 e. printStackTrace (); 20} 21}Main

Tested and found

No data exists in the database.

1.5: Database Connection Pool

I recommend to read write a very detailed http://www.cnblogs.com/xdp-gacl/p/4002804.html