This article has participated in the activity of “New person creation Ceremony”, and started the road of digging gold creation together.
JDBC(Java Database Connectivity), The Java Database connection, is a bridge between Java and Database, is used in the Java language to standardize the client program how to access the Database application program interface, provides the query, update Database and other methods. What JDBC does is simple: Establish a connection to the database, send statements that manipulate the database, and process the results.
Note: If you have the corresponding JAR package ready, you can start with the second one. The scope of this blog is relatively wide, you can choose your own needs in the directory to understand! I wrote the code, all tested again, the code part almost no problem! This blog oneself liver two days and a half, for beginners, should be introduced more comprehensive!
Import the jar package of the corresponding version
Mysql-connector-java is a driver package for Java applications that use JDBC to access the mysql database. Baidu search for Maven, as shown below, click the first link:
Druid connection pool = druid connection pool = druid connection pool = druid connection pool
1. Original JDBC connection:
public class Task {
@Test
public void toTest(a) throws Exception {
//1. Load the Driver (this class) reflection mechanism, do not understand reflection want to know can enter my home page, Java bar view related blog post
Class.forName("com.mysql.cj.jdbc.Driver");
// select * from the database where ysw_blog is the name of the database
Connection con = DriverManager.getConnection(
"JDBC: mysql: / / 127.0.0.1:3306 / ysw_blog"."root"."Your password");
// Prints the hash address of the connection objectSystem.out.println(con); }}Copy the codeTest results screenshot:
2. Druid connection pool mode
For this step, you need to import the jar package from Druid. You can download the jar package from Maven’s home page by following the steps above
The two JAR packages can be found in the lib directory after import
Druid connection pooling
@Test
public void toTest1(a) throws SQLException {
//1. Create a druid data source
DruidDataSource ds = new DruidDataSource();
//2. Configure the incoming parameters to the data source
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl("JDBC: mysql: / / 127.0.0.1:3306 / ysw_blog");
ds.setUsername("root");
ds.setPassword("Your password");
//3. The connection object can be obtained from the data source
DruidPooledConnection con = ds.getConnection();
System.out.println(con);
}
Copy the codeTest results screenshot:
Query and modify operations
1. Use the original JDBC mode to add, delete, modify, and query the database
I. Code section (comments detailed to each step) :
import org.junit.Before;
import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Task1 {
// @suppressWarnings ("all") allows the compiler to stop warning of duplicate lines
// @SuppressWarnings("all")
// Make the connection object a class member so that you don't have to write the part of the code that connects to the database again every time you do a unit test
private Connection con;
/ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * connect to the database * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
// @before annotation: Execute the Before annotation Before the @test annotation
@Before
public void init(a) throws Exception {
//1. Load the driver
Class.forName("com.mysql.cj.jdbc.Driver");
//2. Obtain the connection object
con = DriverManager.getConnection(
"JDBC: mysql: / / 127.0.0.1:3306 / ysw_blog"."root"."Your password");
}
/ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * to add * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
@Test
// Add data to the database using the original JDBC connection
public void toTest0(a) throws Exception {
1. Create an SQL statement template
String sql="INSERT INTO type VALUES(null,? ,?) ;";
//2. Get the object that can execute the SQL statement
PreparedStatement perstmt = con.prepareStatement(sql);
// The placeholder is hello, i.e.?
//3. Assign placeholders to SQL templates
perstmt.setString(1."Professional");
perstmt.setString(2."Players");
/ / 4. Execute SQL
perstmt.executeUpdate();
}
/ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * delete * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
@Test
// Delete the data in the database using the original JDBC connection
public void toTest1(a) throws Exception {
1. Create an SQL statement template
String sql="DELETE FROM type WHERE tname = ? ;";
//2. Get the object that can execute the SQL statement
PreparedStatement perstmt = con.prepareStatement(sql);
// The placeholder is hello, i.e.?
//3. Assign placeholders to SQL templates
perstmt.setString(1."Professional");
/ / 4. Execute SQL
perstmt.executeUpdate();
}
/ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * to * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
// Modify the data in the database using the original JDBC connection
@Test
public void toTest2(a) throws Exception {
1. Create an SQL statement template
String sql="UPDATE type SET route = ? WHERE tname = ? ;";
//2. Get the object that can execute the SQL statement
PreparedStatement perstmt = con.prepareStatement(sql);
// The placeholder is hello, i.e.?
//3. Assign placeholders to SQL templates
perstmt.setString(1."JDBC changed");
perstmt.setString(2."Newly inserted");
/ / 4. Execute SQL
perstmt.executeUpdate();
}
/ * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * look * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * /
// Find the specified data in the database using the original JDBC connection
@Test
public void toTest3(a) throws Exception {
1. Create an SQL statement template
String sql="SELECT * FROM type WHERE tid =? ;";
//2. Get the object that can execute the SQL statement
PreparedStatement perstmt = con.prepareStatement(sql);
// The placeholder is hello, i.e.?
//3. Assign placeholders to SQL templates
perstmt.setInt(1.2);
//4. Execute SQL, return result set rs
ResultSet rs = perstmt.executeQuery();
//5. If there is only one row in the RS, just check whether there is data. If there are multiple rows in the RS, you can use a loop to iterate over each row
if(rs.next()){
// In the query result pointed to by rs.next(), the value is specified by the corresponding column number
int tid = rs.getInt(1);
String tname = rs.getString(2);
String route = rs.getString(3);
// In the query result pointed to by rs.next(), the value is specified by the corresponding column name
//int did = rs.getInt("tid");
//String dname = rs.getString("tname");
//String location = rs.getString("route");System.out.println(tid); System.out.println(tname); System.out.println(route); }}@Test
// Use the original JDBC connection to find data in the database all data
public void toTest4(a) throws Exception {
1. Create an SQL statement template
String sql="SELECT * FROM type;";
//2. Get the object that can execute the SQL statement
PreparedStatement perstmt = con.prepareStatement(sql);
//3. Execute SQL, return result set rs
ResultSet rs = perstmt.executeQuery();
//4. If there is only one row in the RS, just check whether there is data. If there are multiple rows in the RS, you can use a loop to iterate over each row
while (rs.next()){
// In the query result pointed to by rs.next(), the value is specified by the corresponding column name
int tid = rs.getInt("tid");
String tname = rs.getString("tname");
String route = rs.getString("route");
System.out.println(tid+"* * * * *"+tname+"* * * * *"+route); }}}Copy the codeScreenshots after each test part of the above code is run: original database data:
II. Increment (corresponding to toTest0 in the code section above)
Console screenshot after the program runs:
Screenshot of type table corresponding to the database after the program runs:
Ps: Because I inserted two pieces of data in the previous test, all the last pieces of data were inserted into the default ID bit 9
III. Delete (corresponding to code section toTest1 above)
Console screenshot after the program runs:
Screenshot of type table corresponding to the database after the program runs:
IV. Modification (corresponding to toTest2 in the code section above)
Console screenshot after the program runs:
Screenshot of type table corresponding to the database after the program runs:
V. query (corresponding to toTest3 and toTest4 above)
A. Find the specified data in the database and print it to the console:
B. Find all data in the database and print it to the console:
2. Use druid connection pool to add, delete, modify, and query the database
SQL > select * from druid; SQL > select * from druid; For details, see 1. Add, delete, change, and query the database using the original JDBC mode.
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Before;
import org.junit.Test;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Task2 {
// Make the connection object a class member so that you don't have to write the part of the code that connects to the database again every time you do a unit test
private DruidPooledConnection con;
// @before annotation: Execute the Before annotation Before the @test annotation
@Before
public void init(a) throws SQLException {
//1. Create a druid data source
DruidDataSource ds = new DruidDataSource();
//2. Configure connection parameters for the data source
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl("JDBC: mysql: / / 127.0.0.1:3306 / ysw_blog");
ds.setUsername("root");
ds.setPassword("Your password");
//3. The connection object can be obtained from the data source
con = ds.getConnection();
}
// query all data in the hero table
@Test
public void toTest(a) throws SQLException {
1. Create an SQL statement template
String sql="SELECT * FROM hero;";
//2. Get the object that can execute the SQL statement
PreparedStatement perstmt = con.prepareStatement(sql);
//3. Execute SQL, return result set rs
ResultSet rs = perstmt.executeQuery();
//4. If there is only one row in the RS, just check whether there is data. If there are multiple rows in the RS, you can use a loop to iterate over each row
while (rs.next()){
// In the query result pointed to by rs.next(), the value is specified by the corresponding column name
int id = rs.getInt("id");
String name = rs.getString("name");
String age = rs.getString("age");
String sex = rs.getString("sex");
String price = rs.getString("price");
String type_id = rs.getString("type_id");
System.out.println(id+"* * * * *"+name+"* * * * *"+age+"* * * * *"+sex+"* * * * *"+price+"* * * * *"+type_id); }}}Copy the codeHero table data:
The console output from the above code test:
4. Encapsulate the data in query result set RS into list set
Hero class: An object of the Hero class corresponds to a row of data in the rs query set. For example, I have 15 rows of data in the RS query set. I can create 15 Hero objects and encapsulate the queried data into the object.
1. Main class for testing:
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Before;
import org.junit.Test;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class Task2 {
// Make the connection object a class member so that you don't have to write the part of the code that connects to the database again every time you do a unit test
private DruidPooledConnection con;
// @before annotation: Execute the Before annotation Before the @test annotation
@Before
public void init(a) throws SQLException {
//1. Create a druid data source
DruidDataSource ds = new DruidDataSource();
//2. Configure connection parameters for the data source
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl("JDBC: mysql: / / 127.0.0.1:3306 / ysw_blog");
ds.setUsername("root");
ds.setPassword("Your password");
//3. The connection object can be obtained from the data source
con = ds.getConnection();
}
// Query the hero table data and encapsulate it in the List collection
@Test
public void test2(a) throws SQLException {
1. Create an SQL statement template
String sql="SELECT * FROM hero;";
//2. Get the object that can execute the SQL statement
PreparedStatement perstmt = con.prepareStatement(sql);
//3. Execute SQL, return result set rs
ResultSet rs = perstmt.executeQuery();
ArrayList<Hero> list = new ArrayList<>();
//4. If there is only one row in the RS, just check whether there is data. If there are multiple rows in the RS, you can use a loop to iterate over each row
while (rs.next()){
// In the query result pointed to by rs.next(), the value is specified by the corresponding column name
int id = rs.getInt("id");
String name = rs.getString("name");
int age = rs.getInt("age");
String sex = rs.getString("sex");
int price = rs.getInt("price");
int type_id = rs.getInt("type_id");
Hero hero = new Hero(id,name,age,sex,price,type_id);
// The hero object is loaded into the list
list.add(hero);
}
/ / output
for(int i = 0; i < list.size() ; i++) { System.out.println(list.get(i)); }}}Copy the code2. The Hero class used by the above code:
public class Hero {
private int id;
private String name;
private int age;
private String sex;
private int price;
private int type_id;
/ / get set method
public int getId(a) {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName(a) {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge(a) {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getSex(a) {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getPrice(a) {
return price;
}
public void setPrice(int price) {
this.price = price;
}
public int getType_id(a) {
return type_id;
}
public void setType_id(int type_id) {
this.type_id = type_id;
}
// constructor
public Hero(int id, String name, int age, String sex, int price, int type_id) {
this.id = id;
this.name = name;
this.age = age;
this.sex = sex;
this.price = price;
this.type_id = type_id;
}
public Hero(a) {}// re-toString to facilitate output
@Override
public String toString(a) {
return "Hero{" +
"id=" + id +
", name='" + name + '\' ' +
", age=" + age +
", sex='" + sex + '\' ' +
", price=" + price +
", type_id=" + type_id +
'} '; }}Copy the code3. Screenshot of running results:
5. DbUtils operation
1. What is DbUtils
DbUtils is an open source tool class library provided by the Apache organization for simple encapsulation of JDBC, which can simplify the development of JDBC applications.
2. Import the JAR package of DbUtils
To use this, we need to import the jar package of DbUtils, which is also downloaded from Maven. After downloading the JAR package, we can directly drag it into the lib. For details, refer to the beginning of this article. Import the jar package of the corresponding version.
3.DbUtils classes and methods:
QueryRunner class: its main function is to operate the execution of SQL statements, which has query method: the main function is to query the operation and update method: the main function is to add, delete and change the operation. ResultSetHandler interface: its main function is to query the results of the operation
4. Specific operations (code + comments) :
We need to use the Druid connection pool to connect to the database
I.query method use (code + comment + screenshot) :
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import java.sql.SQLException;
import java.util.List;
public class Task3 {
@Test
public void test1(a) throws Exception {
//1. Create a druid data source
DruidDataSource ds = new DruidDataSource();
//2. Configure connection parameters for the data source
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl("JDBC: mysql: / / 127.0.0.1:3306 / ysw_blog");
ds.setUsername("root");
ds.setPassword("Your password");
//3. Create qr object, need to pass data source ds
QueryRunner qr = new QueryRunner(ds);
//4. Query operation, encapsulate the query result level into the Hero object,
// If I have 15 rows of data in my rs set, I need to create 15 Hero objects to encapsulate the query data into the object.
// Load the object into the list collection.
List<Hero> list = qr.query("select * from hero ".new BeanListHandler<Hero>(Hero.class));
/ / output
for (int i = 0; i < list.size(); i++) { System.out.println(list.get(i)); }}}Copy the codeScreenshot of running results:
Ii. update method use (code + comment + screenshot) :
@Test
public void test2(a) throws Exception {
//1. Create a druid data source
DruidDataSource ds = new DruidDataSource();
//2. Configure connection parameters for the data source
ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
ds.setUrl("JDBC: mysql: / / 127.0.0.1:3306 / ysw_blog");
ds.setUsername("root");
ds.setPassword("Your password");
//3. Create qr object, need to pass data source ds
QueryRunner qr = new QueryRunner(ds);
// Add, delete, change, can be directly passed into the execution of SQL statements
/ / to add
qr.update("INSERT INTO type VALUES (NULL,? ,?) ;"."ysw"."blog");
/ / delete
qr.update("DELETE FROM type WHERE tid = ? ;".6);
/ / change
qr.update("UPDATE type SET tname = 'YuShiwen' WHERE tname = ? ;"."ysw");
}
Copy the codeAdd, delete and modify operation screenshots:
Table type:
Perform the console output and type table corresponding to the add operation:
Perform the corresponding console output and type table for the delete operation:
Perform the console output and type table corresponding to the modify operation:
