preface
Hello, I’m Bigsai, today we have JDBC+MySQL combat, very suitable for beginners to open a new world of friends. Implement a curD example. Praise first and then watch, handsome boy beautiful girl form a good habit!
Before proceeding with this example, make sure you have the MySQL and IDEA compilers on your computer, and install Navicat, the MySQL database connection management tool. The following operations can be performed.
If you haven’t seen or used it before don’t worry, this course offers dumb-ass instruction and you just have to follow my instructions to get it done.
Through this book you can master the following:
- MySQL database creation
- Create the JDBC+MySQL project
- MySQL > add, delete, change, and check MySQL
Case analysis
What is the result of this case? This case realizes the management of a student’s information, that is, the increase, deletion, change and check of the student’s personal information.
Core ideas disassemble
How should we design this project?
Don’t worry, don’t worry. Just listen to me. For the actual combat of this case, aiming to achieve the operation of MySQL with Java language (JDBC), the core of the spindle is MySQL data, so we start from MySQL, the process of the whole case can be roughly divided into:
- Analyze design requirements and create MySQL databases and tables
- Create a Java project and add JDBC and Junit unit test dependencies
- Write project code to achieve JDBC add, delete, change and check cases
- Unit testing
For JDBC programming, it needs to connect and interact with MySQL, and it also has a certain flow sequence to follow, usually the following six flows:
- 1: Register the driver (only once)
- 2: Establish a Connection
- 3: Create Statement to run SQL
- 4: Runs the statement
- 5: Processing running results (ResultSet)
- 6: Release resources
Case knowledge
This case mainly involves the knowledge of MySQL and JDBC. These two concepts are very common techniques for Java Web development, and I will introduce them to you.
MySQL: MySQL is one of the most popular databases, and free and open source, database is used to store data, in terms of data format, there are relational data and non-relational data,MySQL is a relational database. In terms of the Web, MySQL is one of the best RDBMSS ** : MySQL SQL statement is one of the standards of common relational databases. It has the advantages of fast speed, small size and low cost. It is one of the best ways for small and medium-sized enterprises to learn and use it.
Using JDBC in Java allows you to quickly use MySQL in your applications. Besides DDL** (Database Schema Definition Language: Data Definition Language)And DMLIn addition to traditional Manipulation such as **, you can use most of the features of MySQL through JDBC. In addition, MySQL provides the index optimization query speed, using B+ tree or hash indexes to meet the query performance requirements in different scenarios. It also provides views, functions, constraints, triggers and other functions to make the database suitable for more scenarios. MySQL provides transaction, rollback and other mechanisms to ensure data security consistency.
JDBC
Java Database Connectivity (JDBC) is an application programming interface in the Java language that regulates how clients access a Database (java.sql in the JDK). JDBC, as we call it, is relational-database oriented, providing methods such as querying, updating, deleting, and adding data to a database. You need to import specific JAR packages. The JAR packages need to be imported for different databases.
JDBC has the following apis:
- DriverManager: Loads different drivers and returns the corresponding database Connection to the caller according to the request.
- Driver: A Driver that loads itself into DriverManager, processes the request and returns the Connection.
- Connection: Database Connection, responsible for communication with the database, SQL execution and transaction processing are performed in a specific Connection environment. You can create statements that execute SQL, preparedStatements, and so on.
- **Statement: ** Is used to execute SQL queries and updates (for static SQL statements and single executions).
- PreparedStatement: It is used to execute SQL queries containing dynamic parameters and update them (compiled on the server side, allowing repeated execution to improve efficiency). PreparedStatement is used in this case.
- **CallableStatement: ** Used to call stored procedures in the database.
- **SQLException: ** indicates that an exception (an error) occurred during the establishment and closure of a database connection and the execution of an SQL statement.
I think now you have a clearer understanding of MySQL and JDBC, MySQL it is a database for us to use, and JDBC is Java to use MySQL a tool and bridge, combined with the above knowledge, let’s start to combat it!
The first level creates the database and project
Before the actual combat, you need to create a good database and project to carry out JDBC add, delete, change and check.
Creating a database
We use Navicat to create databases. You may be wondering what Navicat is and what it does.
Navicat is a database management tool, it can not directly modify MySQL data and structure, but through your visual interface operation, it helps you to execute SQL code, because considering that if you directly use SQL database operation, it may not be very intuitive and difficult to learn. Navicat management database can clearly see the database structure, data, Settings can be easily changed, greatly improve the development efficiency and reduce errors to a certain extent. Please refer to the following figure:
Open Navicat and connect to the database. Right-click to create a database named Boxuegu and use the default character set and collation rules.
Then open the Boxuegu database, right-click in the column of the table to create a new table, and set the four fields ID, NAME, age, high. Id is set as the primary key and increases automatically.
Save this table and name it student for student. This completes the addition of the student table. Where id is the auto-increment primary key, the id will be automatically incremented if the key is not inserted. Name is the name of the student, and age is the age of the student. High represents the height of a student, and we use these fields to represent the information of a student.
Of course, if you don’t like to use database management tools to do this, and prefer the console or command line to create databases and tables, of course, this is ok:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`high` int(11) NOT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the codeIn this way, our database and table will be created. We will use JDBC to add, delete, change and verify the MySQL table. Let you manipulate the data.
Create a project
We create a Java project to introduce JDBC dependency, here I do not let you go to find JDBC JAR package, using Maven method to introduce JAR package, so we use IDEA to create Maven project.
First open IDEA, create the Maven project, and click Next.
Next fill in GroupId and ArtifactId, which you can do yourself. In this case fill in com and JDBCTest. Click Next.
Next, select a folder on disk as the path to the project and click Finish to create the project. Next we need to add MySQL’s JDBC dependencies and Junit unit test dependencies by adding the following to the project pom.xml file:
<dependencies>
<! -- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
Copy the codeThen the project is successfully created and the following classes are created:
- Java directory in main: SqlManage class, used to write SQL related statements
- Student = student; student = student; student = student
- In the Java directory of Test: the test class, used to write unit tests.
With JDBC environment just write the right code to run! The final catalog for the project looks like this:
The second level is JDBC insertion and query
We have covered the six steps of performing JDBC operations above, but some steps do not need to be repeated, such as loading the driver, establishing a connection, closing, etc., which can be reused. In addition, we mapped the data in MySQL to objects in Java in the project (that is, each record in MySQL table can generate a Java object), and the design of functions in the project was based on Java objects instead of data fields in MySQL, which reduced the coupling of modules in the project.
Preparatory work
Start by writing the following in the Student class, which corresponds to the Student table in the MySQL database.
public class student {
private int id;// select id from student
private String name;// select name from student
private int age;// Select * from student
private int high;// The height is set to student high
// constructor with id (used when querying)
public student(int id, String name, int age, int high) {
this.id = id;
this.name = name;
this.age = age;
this.high = high;
}
// constructor without id (used when inserting)
public student(String name, int age, int high) {
this.name = name;
this.age = age;
this.high = high;
}
// The toString() method, used for console print tests
@Override
public String toString(a) {
return "student{" +
"id=" + id +
", name='" + name + '\' ' +
", age=" + age +
", high=" + high +
"}\n";
}
// The get set method is used to set the value
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 int getHigh(a) {
return high;
}
public void setHigh(int high) {
this.high = high; }}Copy the codeNext we move on to the SQLManage class, where we encapsulate some of the JDBC operations. The initialization function registers the driver and establishes the connection. Write the following in SQLManage:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class sqlmanage {
private Connection con=null;// The database connection, obtained from the DriverManager method, is used to generate preparedStatements that execute SQL
public sqlmanage(a) throws SQLException, ClassNotFoundException {
//step1 load the database driver
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Database driver loaded successfully");
//step2 connect to database
this.con = DriverManager.getConnection("jdbc:mysql://localhost:3306/boxuegu? useSSL=false"."root"."bigsai66");
System.out.println("Database connection successful");
}
public void close(a) throws SQLException {
this.con.close();; }}Copy the codeWhere Connection is established through Connection, which is an interface class. Its function is to connect (session) to the database. Establish a Connection interface class objects: Connection conn = DriverManager. GetConnection (url, user, password); The URL must be in the following format:
jdbc:mysql://<host>:<port>/<database_name>? Property1 = value1 & property2 = value2...- Host indicates the IP address or domain name of the host. You can use localhost or 127.0.0.1 locally.
- Database_name indicates the name of the database created in MySQL. In this case, the database name is Boxuegu.
- Property configures properties such as code, time zone, etc.
User is the user name for logging in to MySQL, and password is the password for logging in to MySQL.
A single insert
MySQL insert a simple SQL statement:
insert intoTable name (field1, the field2. Field n)value(value1And the value2. The value of n);Copy the code**insertStudent(student student)** insertStudent(student student)
public void insertStudent(student student) throws SQLException {
// Create an SQL statement
String sql="insert into student(name,age,high)value(? ,? ,?) ";
PreparedStatement can precompile SQL statements, which prevents SQL injection and improves security.
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1,student.getName());
ps.setInt(2,student.getAge());
ps.setInt(3,student.getHigh());
// execute SQL because insert operations are not handled here
ps.executeUpdate();
ps.close();
}
Copy the codeWrite the **insertTest()** function in the test class to test the insertion of three students’ records:
@Test
public void insertTest(a) throws SQLException, ClassNotFoundException {
sqlmanage sqlmanage=new sqlmanage();
sqlmanage.insertStudent(new student("bigsai".22.180));
sqlmanage.insertStudent(new student("bigpian".21.165));
sqlmanage.insertStudent(new student("doudou".20.160));
}
Copy the codeAlter table student; alter table student; alter table student;
Bulk insert
What do you do if you have 100 or more sets of data to insert at the same time?
-
The loop inserts one by one
-
Batch inserts multiple at a time
Batch processing saves a lot of time and improves the efficiency of the system. So you can choose multiple inserts when you have a lot of data.
Batch processing saves a lot of time and improves the efficiency of the system. So you can choose multiple inserts when you have a lot of data.The **insertStudents(Student Students [])** function is used in sqlManage to insert student records in batches:
public void insertStudents(student students[]) throws SQLException {
this.con.setAutoCommit(false);//The String is not submitted automaticallysql="insert into student(name,age,high)value(? ,? ,?) "; PreparedStatement ps=con.prepareStatement(sql);
for(int i=0; i<students.length; i++)
{
ps.setString(1,students[i].getName());
ps.setInt(2,students[i].getAge());
ps.setInt(3,students[i].getHigh());
ps.addBatch();
if(i%1000==0)
{
ps.executeBatch();
}
}
ps.executeBatch();
con.commit();
ps.close();
}
Copy the codeAmong them:
- Con.setautocommit (false) means do not commit automatically, because we usually commit manually to improve efficiency when batch processing.
- Multiple group inserts are performed primarily through addBatch() of the PreparedStatement object and are not committed.
- ExecuteBatch () will commit if it is set not to commit automatically, but it has been set not to commit to reduce the number of commits. It is actually committed to MySQL if and only if con.mit () is executed.
We then write insertStudentsTest() in the test unit test to test the bulk inserts:
@Test
public void insertStudentsTest(a) throws SQLException, ClassNotFoundException {
sqlmanage sqlmanage=new sqlmanage();
student students[]=new student[5];
for(int i=0; i<students.length; i++) { students[i]=new student("Passers-by"+i,12.156);
}
sqlmanage.insertStudents(students);
}
Copy the codeAfter executing, you can see that the data is successfully inserted into MySQL:
Query data
Queries are used far more often than other operations, and although some complex table queries can be a pain, the basic SQL query is relatively simple, with the syntax format:
selectfield1, the field2And...fromThe table name [whereConditions]//way1
select * fromThe table name [whereConditions]//way2
Copy the codeIf there is no WHERE condition, the entire table is queried by default. Here we query all records based on this condition: query returns all students under the age of 21.
Select ** getallStudent ()** from student where age < 21; select ** getallStudent ()** from student where age < 21;
public List<student> getallstudent(a) throws SQLException {
List<student>list=new ArrayList<student>();// The result returned
String sql = "select * from student where age < 21";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet resultSet=ps.executeQuery();/ / the result set
while(resultSet.next())/ / traverse
{
int id=resultSet.getInt(1);
String name=resultSet.getString(2);
int age=resultSet.getInt(3);
int high=resultSet.getInt(4);
list.add(new student(id,name,age,high));
}
ps.close();
return list;
}
Copy the codeThe return value of the insert mentioned above does not need special handling because the return is an insert success message, and the query step strictly follows the six JDBC steps – because the returned data needs to be processed. We mainly process the returned data through a ResultSet.
In the process of iterating the value of a ResultSet, the value is automatically converted to the corresponding Java data type by using getXXX(int Index). Index is the number of the table column in the MySQL database and the corresponding serial number of the data in the ResultSet.
Write the selectTest() test function in the test unit test using the above function to print all records from the database whose age is less than 21 on the console:
@Test
public void selectTest(a) throws SQLException, ClassNotFoundException {
sqlmanage sqlmanage=new sqlmanage();
List<student>list=sqlmanage.getallstudent();
System.out.println(list.toString());
}
Copy the codeYou can see that the data was successfully queried:
JDBC modification and deletion
Modify the data
To modify a certain record, we usually make modifications based on the primary key of the message. Otherwise, the entire table will be modified. MySQL > alter table MySQL > alter table MySQL > alter table MySQL
Update the table namesetfield=Value [whereConditions];-- It is recommended to have where: otherwise, every data will be modified
Copy the codeHere we assume that the name of all the student records whose age is 12 is set to xiaoming. Write the **update()** function in SQLManage to make changes:
public void update(a) throws SQLException {
String sql = "update student set name='xiaoming' where age=12";
PreparedStatement ps = con.prepareStatement(sql);
ps.executeUpdate();
ps.close();
}
Copy the codeWrite the **updateTest()** function in the TES unit test to call the UPDATE () method of the SQLManage class and set the name of all the records in the Student table whose age is 12 to xiaoming.
@Test
public void updateTest(a) throws SQLException, ClassNotFoundException {
sqlmanage sqlmanage=new sqlmanage();
sqlmanage.update();
}
Copy the codeRefresh Navicat and you will find that the data has been successfully updated:
Delete the data
JDBC deletion is also a common operation. However, be careful when deleting JDBC data. Delete statements generally have where conditions, otherwise all records will be deleted. SQL statement to be deleted:
delete fromThe table name [whereConditions];Copy the codeSqlmanage deleteByID (int ID); sqlManage deleteByID (int ID);
public void deletebyid(int id) throws SQLException {
String sql = "delete from student where id = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1,id);
ps.executeUpdate();
ps.close();
}
Copy the code**deleteTest()** Delete student (id 1); :
@Test
public void deleteTest(a) throws SQLException, ClassNotFoundException {
sqlmanage sqlmanage=new sqlmanage();
sqlmanage.deletebyid(1);
}
Copy the codeCheck database discoveryThe record whose ID is 1[bigsai] was deleted successfully:
Summary and Expansion
conclusion
Ok, this JDBC+MySQL add, delete, change check case here. Looking back on this course, do you feel that everything is not so difficult, just need to step by step to complete the whole case? Let’s review the process:
- Create databases and tables
- Create the project to add JDBC and Junit unit test dependencies
- Writing project code (JDBC 6 steps)
- Unit testing
Each process represents a knowledge point, of which the most important is to master the JDBC programming process, master its method, if forgotten, also need to review more.
expand
MySQL This example only uses SQL statements corresponding to the four basic operations: INSERT (insert), SELECT (query), update(modify), and delete(delete). If you need to learn more about MySQL, you need to dig into many aspects, from SQL statements you also need to learn MySQL complex queries such as relational queries, grouping, sorting and so on. In terms of performance, pay attention to the MySQL index type and understand its underlying data structure and advantages. From security and data consistency you will learn about MySQL dirty read, magic read, and various locks.
JDBC is a very native way to deal with MySQL in Java, and to the later with the in-depth learning you will contact two ORM framework – Mybatis and Hibernate, of course, in the domestic Mybatis to some more popular. These two frameworks encapsulate JDBC and make it easier for developers to use relational databases such as MySQL in their applications. They are widely used in Web projects. Whether SSH(Spring,Strus2,Hibernate) or SSM(Spring,SpringMVC,Mybatis), its framework as persistence layer is based on the encapsulation processing of JDBC. So remember to review the content of JDBC while learning the new framework in the future, and master the underlying principles of JDBC!
All right, class dismissed! I’ll see you soon!
Finally, this article is included in the public account: Bigsai, more wonderful and information to share with you, welcome Mark to grow up with you! Also included in erudite Valley in a new form of levels, welcome to try!