JDBC Connection MySQL

Java provides a series of API--JDBC for database programming, in fact, it is through the interface defines a set of access to the database specification, and the implementation of these specifications are the database vendors, and the interface implementation encapsulated as. jar package, and we programmers only need to get the database vendor encapsulated. jar Package, The JDBC API is called to connect to the corresponding database. The call to call the JDBC API is unified.

Common APIs in JDBC:

Class 1.DriverManager-Drive Manager

Class 2.Connection-Connecting programs and databases

3.Statement class-SQL statement class

Class 4.PrepareStatement-This class is a subclass of the statement class

5.ResultSet class-result set, result is the meaning of the results, set is the set

Here's how:

1, load driver---JDK1.8 can omit this code try {class.forname ("com.mysql.jdbc.Driver");//Reflection Code---class loading} catch (ClassNotFoundException E {//TODO auto-generated catch Blocke.printstacktrace ();} 2. Get the connection connection con = null; String url = "Jdbc:mysql://localhost:3306/test129?usessl=true&useunicode=true&characterencoding=utf8";// Protocol://IP Address: Port number/database name string username = "root"; String password = "root"; try {con = drivermanager.getconnection (Url,username,password);//Connection database requires three parameters: url/account/Password//3, Build Statement Object String sql = "";//write SQL statement Statement state = Con.createstatement ();//Get Statement object//4 by connection, statement object execution Sqlint r = State.executeupdate (SQL);//All DML statements call this method, which by default returns the number of rows */* that are affected by the new data, and sometimes the auto-generated primary key value is required. int r = state.executeupdate (sql, Statement.return_generated_keys); ResultSet rs = State.getgeneratedkeys (); if (Rs.next ()) {int id = rs.getint (1);} */} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} FINALLY{//5, close connection if (con! = null) {try {C On.close ();} catch (SQLException e) {//TODO auto-generated catch BLOCKE.PRIntstacktrace ();}}} 

The above is the connection to the MySQL database and the increment/delete/change (DML) operation on the table, while the query (DQL) operation requires only the following changes to the code:

　　

String sql = "SELECT * from T_group"; Statement state = Con.createstatement (); ResultSet rs = state.executequery (SQL);//dql statement General this method while (Rs.next ()) {Groupbean GB = new Groupbean (); Gb.setid ( Rs.getint ("Pk_groupid")), Gb.setgroupname (rs.getstring ("F_groupname")), Gb.setgroupaddress (rs.getString ("F_ Groupaddress ")); Gb.setgroupnum (Rs.getint (" F_groupnum ")); Allgroup.add (GB);}

The connection and shutdown are invariant, the SQL statement is changed, the data returned by the query statement is also different, and is returned as a result set resultset. Next () Method--Probe for the next message, return a Boolean value, You can use loops to load query results into JavaBean objects that correspond to data tables.

The Relationship between table and table in the design database and the Bean object in Java is relative to the bean. The most common are three relationships:

	Table	Bean
One	The relationship between the two tables is that the foreign key of one of the tables is associated with another table, the foreign key must have a unique constraint, or it can be a foreign key association of two tables, plus a unique constraint	Two classes, where one class has a another class;
One-to-many	A main table, many from the table, all the foreign keys are on the more side of the	A single party has a multiparty collection, and the multiparty has a one-sided
Many-to-many	Two (or more) primary tables, one for all foreign keys on the relational table	Two classes each other has a set of each other type

The specific relationship depends on the business;

Many times you need to use user input content to build SQL statements, you will encounter the risk of SQL injection, you can use the Preparestatement class instead of the statement class to install SQL statements, if there is a user table:

　　

String sql = "SELECT * from t_user where f_username =?" and F_password =? "; /preparestatement features, the standard SQL template is written in advance, the unknown part with the number instead of preparedstatement PS = con.preparestatement (SQL); The template is passed into the preparestatement construction method to produce the object ps.setstring (1, inputusername);//The position of the incoming question mark, and the substitution statement, where the first position is 1, not 0ps.setstring (2, Inputpassword); ResultSet rs = Ps.executequery ();//submits the complete SQL statement UserBean user = Null;while (Rs.next ()) {user = new UserBean (); User.setid ( Rs.getint ("pk_id")), User.setusername (rs.getstring ("F_username")), User.setpassword (rs.getstring ("F_password"));

Transaction--because of a business, it is necessary to execute multiple DML statements at the same time, succeed together and fail together:

　　

      try {con = drivermanager.getconnection (Url,username,password);                 1, the automatic submission of the connection object is set to False                Con.setautocommit (false);                Write the SQL statement here                Con.commit ();//2, manually submit                  }catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace (); try {con.rollback ();//3, rollback, if there is a failure, all the way back to the look before the change} catch (SQLException E1) {//TODO Auto-generated catch Blocke1.printstacktrace ();}              } FINALLY{//5, close connection if (con! = null) {try {con.close ();} catch (SQLException e) {//TODO auto-generated catch Blocke.printstack Trace ();}}               }    

　　

　　

JDBC Connection MySQL