1. Know ABOUT JDBC
JDBC is an API for connecting to a database in Java language programming.JDBC is a specification that provides a set of interfaces that allow access to the underlying database API in a portable way. Use the JDBC driver to access the database and store data to the database.
Explain the two pictures above:
Java applications connect to THE JDBC Driver through the JDBC API. These JDBC drivers are provided by various database manufacturers for JDBC. We can download jar packages on the Internet to use, and then connect to our database through the JDBC Driver.
The first step is to add the driver
1. Create a folder in the project as lib 2. Copy the Mysql driver package to this folder 3Copy the codeStep 2 Connect to the database
package con.meils.jdbc.conn; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ConnectionClass { public static void main(String[] args) throws ClassNotFoundException, SQLException {// TODO auto-generated method stub // TODO auto-generated method stub // // After loading the bytecode, instantiate a drive class.forname ("com.mysql.jdbc.Driver"); // 2, connect String url ="jdbc:mysql://localhost:3306/mytest";
String username = "root";
String password = "zjj19970517"; Connection connection = DriverManager.getConnection(url, username, password); System.out.println(connection); // If there is output, the connection is successful}}Copy the codeStep 3 Create a table by manipulating the database
package con.meils.jdbc.ddl; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class CreateTable { public static void main(String[] args) throws Exception { // TODO Auto-generated method stub // when a bytecode is loaded into the JVM, static code blocks in the bytecode file are executed. // When the bytecode is loaded into the JVM, a Driver is instantiated."com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbc_db";
String username = "root";
String password = "zjj19970517"; / / 2 \ connect to the database Connection Connection = DriverManager. GetConnection (url, username, password); // 3\ create SQL statement String SQL ="create table stu (id int , name varchar(20), age int)"; / / 4 \ executing SQL statements Statement st = connection. The createStatement (); int row = st.executeUpdate(sql); // 5\ release st.close(); connection.close(); }}Copy the code
Why release resources?
Step 4 Insert data
package con.meils.jdbc.dml; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class InsertClass { public static void main(String[] args) { // TODO Auto-generated method stub // = = = = = = = = = = = = = = = = insert data = = = = = = = = = = = = = = = = Connection conn = null; Statement st = null; Try {// 1, load driver class.forname ("com.mysql.jdbc.Driver"); // 2, create connection String url ="jdbc:mysql://localhost:3306/mytest";
String user = "root";
String password = "zjj19970517"; conn = DriverManager.getConnection(url, user, password); // create SQL statement String SQL ="insert into stu values(1, 'zjj', 20)"; st = conn.createStatement(); Int row = st.executeUpdate(SQL); System.out.println(row); }catch (Exception e) { e.printStackTrace(); } finally {// 5if(st!=null) {
try {
st.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(conn! =null) { try { conn.close(); }catch (Exception e) { e.printStackTrace(); } } } } }Copy the codeStep 5 Query operations
package con.meils.jdbc.dql; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class QueryClass { public static void main(String[] args) throws SQLException, ClassNotFoundException {/ / TODO Auto - generated method stub / / = = = = = = = = = = = = = = = = query data = = = = = = = = = = = = = = = = / / / / 1 \ load driver // When a bytecode is loaded into the JVM, static code blocks in the bytecode file are executed. // When the bytecode is loaded, a Driver Class."com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mytest";
String username = "root";
String password = "zjj19970517"; / / 2 \ connect to the database Connection Connection = DriverManager. GetConnection (url, username, password); // 3\ create SQL statement String SQL ="select count(*) as total from stu"; How many rows of data /, / / query / 4 \ executing SQL statements Statement st = connection. The createStatement (); ResultSet rs = st.executeQuery(sql);if(rs.next()) {
int count = rs.getInt("total"); System.out.println(count); // 1} // 5\ release st.close(); connection.close(); }}Copy the code
Comparison table of data types:
We have basically learned how to deal with the database above, but there are many disadvantages in this way, such as: every step of the operation is a database connection, resulting in waste, so we need to use the IDEA of DAO to deal with in practice.
Second, DAO design
1. What is DAO
The DAO (Data Access Object) is a Data storage Object between the service logic layer and the persistence layer to implement persistent Data Access.
When not using DAO:
When using DAO:
ORM mapping
Fourth, the Domain
1. Know domain
2, domain classes
3. Save data
4. Get data
5. DAO design specification
case
We can write a DAO interface to define common database operation methods. At this time, we will connect multiple databases at the same time, such as mysql and Oracle. We can implement oracleDao and mysqlDao classes respectively. The functions implemented are indeed the same.
So interface oriented programming is important and a good way to do it.
1. Package naming specification
2. Class naming conventions
3. Development steps
Directory as follows
Content implementation:
The details of the interface implementation class:
package com.meils.jdbc.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.meils.jdbc.dao.IStudentDao; import com.meils.jdbc.domain.Student; import com.meils.jdbc.utils.JdbcUtil; @author apple ** / public class StudentDaoImpl implements IStudentDao{@override /** * Implements student * @param stu */ public void save(Student stu) { // TODO Auto-generated method stub Connection conn = null; PreparedStatement ps = null; Conn = jdbcutil.getConnection (); conn = jdbcutil.getConnection (); String sql ="insert into student (name, age) values (? , ?)"; Ps = conn.prepareStatement(SQL); ps.setString(1, stu.getName()); ps.setInt(2, stu.getAge()); Ps.executeupdate (); } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.close(conn, ps, null); } } @Override public void delete(int id) { Connection conn = null; PreparedStatement ps = null; Conn = jdbcutil.getConnection (); conn = jdbcutil.getConnection (); String sql ="delete from student where id = ?"; Ps = conn.prepareStatement(SQL); ps.setInt(1, id); Ps.executeupdate (); } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.close(conn, ps, null); } } @Override public void update(int id, Student stu) { Connection conn = null; PreparedStatement ps = null; try { conn = JdbcUtil.getConnection(); String sql ="update student set name=? , age=? where id = ? "; ps = conn.prepareStatement(sql); ps.setString(1, stu.getName()); ps.setInt(2, stu.getAge()); ps.setInt(3, id); / / execution ps. ExecuteUpdate (); } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtil.close(conn, ps, null); } } @Override public Student findOne(int id) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); // SQL statement String SQL ="select * from student where id = ?"; ps = conn.prepareStatement(sql); ps.setInt(1, id); // execute rs = ps.executeQuery();if(rs.next()) {// Save data Student stu = new Student; stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age"));
stu.setId(rs.getInt("id"));
returnstu; } } catch (Exception e) { e.printStackTrace(); } finally {// Destroy jdbcutil. close(conn, ps, rs); }return null;
}
@Override
public List<Student> findAll() {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConnection();
st = conn.createStatement();
String sql = "select * from student ";
rs = st.executeQuery(sql);
List<Student> list = new ArrayList<Student>();
while (rs.next()) {
Student stu = new Student();
stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age"));
stu.setId(rs.getInt("id"));
list.add(stu);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtil.close(conn, st, rs);
}
returnnull; }}Copy the codeWriting test classes:
package com.meils.jdbc.dao.test; import java.util.List; import org.junit.Test; import com.meils.jdbc.dao.IStudentDao; import com.meils.jdbc.dao.impl.StudentDaoImpl; import com.meils.jdbc.domain.Student; @author apple */ public class StudentDaoTest {public static void main(String[] args) {// TODO Auto-generated method stub } @Test public voidsave() {
Student stu = new Student();
stu.setName("Zhang wei");
stu.setAge(21);
IStudentDao dao = new StudentDaoImpl();
dao.save(stu);
}
@Test
public void delete() {
IStudentDao dao = new StudentDaoImpl();
dao.delete(4);
}
@Test
public void update () {
Student stu = new Student();
stu.setName("mmmmm");
stu.setAge(16);
IStudentDao dao = new StudentDaoImpl();
dao.update(5, stu);
}
@Test
public void findOne() {
IStudentDao dao = new StudentDaoImpl();
Student stu = dao.findOne(5);
System.out.println(stu);
}
@Test
public void findAll() { IStudentDao dao = new StudentDaoImpl(); List<Student> allStu = dao.findAll(); System.out.println(allStu); }}Copy the codeJDBC utility classes:
package com.meils.jdbc.utils; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /** * JDBC tools * @author apple ** / public class JdbcUtil {// config information public static String driverName ="com.mysql.jdbc.Driver";
public static String url = "jdbc:mysql://localhost:3306/mytest";
public static String userName = "root";
public static String password = "zjj19970517"; Static {try {// Load the driver. Class. ForName (jdbcutil.drivername) is loaded only once because it is in a static block. }catch (Exception e) { e.printStackTrace(); }} /** * Connect to the database * @returnReturn the Connection object */ public static ConnectiongetConnection() {
try {
return DriverManager.getConnection(JdbcUtil.url, JdbcUtil.userName, JdbcUtil.password);
} catch (Exception e) {
e.printStackTrace();
}
returnnull; } /** * Close the connection, * @param conn * @param st * @param rs */ public static void close(Connection conn, Statement st, ResultSet rs) {if(conn!=null) {
try {
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
if(st!=null) {
try {
st.close();
}catch(Exception e) {
e.printStackTrace();
}
}
if(rs! =null) { try { rs.close(); }catch(Exception e) { e.printStackTrace(); }}}}Copy the codeGithub address of the project
3. DAO code refactoring
4. Statement interface
1 Statement is the parent interface, PreparedStatement and CallableStatement are the sub-interfaces. 2 Statement is used to send data between a Java application and a database, such as SQL statements, to perform operations on the database. PreparedStatement 4 PreparedStatement is used to precompile a template SQL Statement, which can be used at runtime when passing a parameter. 5 CallableStatement Is used when accessing a stored procedureCopy the code5. Precompiled statements
Prevent SQL injection:
Java JDBC DAO Encapsulation tutorial
6, through DB class to operate the database
Another way to encapsulate JDBC:
package com.meils.jdbc.db; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class MysqlDB { Connection conn = null; Statement st = null; PreparedStatement ps = null; ResultSet rs1 = null; // result set // configuration information // driverName private static final String driverName ="com.mysql.jdbc.Driver"; // Database address private static Final String URL ="jdbc:mysql://localhost:3306/mytest"; // Database login userName private static final String userName ="root"; // Database password private static final Stringpwd = "zjj19970517"; /** * Connect to database * @return
*/
public Connection getConnection() {
try {
Class.forName(driverName);
this.conn = DriverManager.getConnection(URL, userName, pwd);
System.out.println("Connection successful");
return conn;
} catch (Exception e) {
e.printStackTrace();
}
returnnull; } // Constructor publicMysqlDB() { this.getConnection(); } /** * query * @param SQL SQL statement, complete statement, query general security * @return
*/
public ResultSet query(String sql) {
try {
this.st = this.conn.createStatement();
this.rs1 = this.st.executeQuery(sql);
return rs1;
} catch (SQLException e) {
e.printStackTrace();
}
returnnull; @param args // array of parameters */ public void update(String SQL, String [] args) { try { this.ps = this.conn.prepareStatement(sql);for(int i = 0 ; i < args.length ; i++) { this.ps.setString(i+1, args[i]); } this.ps.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { this.close(); } /** * delete * @param SQL * @return
*/
public int delete(String sql){
try {
this.st = this.conn.createStatement();
int num = this.st.executeUpdate(sql);
return num;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.close();
}
return0; } /** * release resources */ public voidclose () {
try {
if(rs1! =null) { rs1.close(); }if(st! =null) { st.close(); }if(ps! =null) { ps.close(); }if(conn! =null) { conn.close(); } } catch (SQLException e) { // TODO: handle exception e.printStackTrace(); }}}Copy the codeThe following tests:
package com.meils.jdbc.db; import java.sql.ResultSet; import java.sql.SQLException; public class Test { public static void main(String[] args) { // TODO Auto-generated method stub // Conn(); delete(); } // Test connection public static voidConn() { MysqlDB m = new MysqlDB(); } // Test query public static voidqueryTest() {
MysqlDB m = new MysqlDB();
String sql = "select * from student";
ResultSet result = m.query(sql);
System.out.println(result);
try {
while (result.next()) {
System.out.println(result.getString("name")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { m.close(); } // Update public static voidupdate() {
MysqlDB m = new MysqlDB();
String sql = "update student set name = ? where id = ?";
String [] args = {"Plum 111"."Seven"}; m.update(sql, args); } // Delete public static voiddelete() {
MysqlDB m = new MysqlDB();
String sql = "delete from student where id = 8"; System.out.println(m.delete(sql)); }}Copy the code7. Call the stored procedure
First, let’s get familiar with stored procedures
DELIMITER // Create PROCEDURE getStudent1(in n varchar(20))
BEGIN
select * from student wherename = n; END // DELIMITER ; // Run call getStudent1('Cheung Kam Kit');
Copy the code
package com.meils.jdbc.dao.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import com.meils.jdbc.domain.Student; import com.meils.jdbc.utils.JdbcUtil; public class ProductTest { public static void main(String[] args) { CallableStatement cstmt = null; Conn = jdbcutil.getConnection (); // conn = jdbcutil.getConnection (); //2. CallableStatement cs = conn.prepareCall("{ call getStu(?) }");
//3.设置参数
cs.setString(1, "Zhang Kam Kit"); //4. Run ResultSet rs = cs.executeQuery();if(rs.next()) {
Student stu = new Student();
stu.setId(rs.getInt("id"));
stu.setName(rs.getString("name"));
stu.setAge(rs.getInt("age")); System.out.println(stu); } } catch (Exception e) { e.printStackTrace(); }}}Copy the codeA stored procedure with parameters and output
DELIMITER // CREATE PROCEDURE getName (IN I INT, OUT n VARCHAR ( 50 ) ) BEGIN SELECT NAME INTO n FROM student WHERE id = i; END // DELIMITER; call getName(1, @name); select @name;Copy the code
package com.meils.jdbc.dao.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import com.meils.jdbc.domain.Student; import com.meils.jdbc.utils.JdbcUtil; public class ProductTest { public static void main(String[] args) { CallableStatement cstmt = null; Conn = jdbcutil.getConnection (); // conn = jdbcutil.getConnection (); //2. Create precompiled statement CallableStatement cs = conn.prepaRecall ("{ call getName(? ,?) }"); //3. Set cs.setint (1, 1); //4. Set cs.registerOutParameter(2, types.varchar); / / 5. Perform cs. The execute (); String name = cs.getString(2); System.out.println(name); } catch (Exception e) {e.printStackTrace(); }}}Copy the code8, things
First, turn off the automatic commit transaction before reducing the money. Multiple operations can be performed during this time. At this point, the transaction commit must be manual, using conn.mit (); . If an exception occurs in the middle, a rollback is performed to free the resource.
9. Batch processing
Normally, if we want to execute many SQL statements at once, we execute one statement and then the next. When we use batch processing, we can execute many statements at once, and the call cost event is greatly shortened.
10. Save the file to the database
Usually we don’t do this because database space is very valuable. Large files take up more space and cost more. If we need to store files, then we need to choose bloB.
You first need to set the field type in the database to BLOB.Copy the codeStore files to database:
Fetch the file from the database:
Get the automatically generated primary key ID
Demand analysis:
We may back to encounter such a situation, we first registration information, fill in the user and password after, registration is successful, the database id is automatically added, then to improve their own information back to yourself, it will need we just created id, so how do you return id give us next time use?
There are two ways to obtain:
1:
2:
