In the previous section, we successfully passed data from the JSP page in the foreground to the controller layer, but we haven't written the service layer yet. To be honest, there's still a lot of work to do, especially with links to databases. So in this section, we'll focus on database connections.
01 Preamble
You may have heard many new terms before, such as data source, connection pool, c3p0, etc.As a novice, it's easy to be intimidated by these terms, because most training organizations don't tell you about them. They just tell you the most basic jdbc. Generally speaking, they tell you a few steps to use Java code to operate a database.
First, load the driver class (mom's a chicken cake, for beginners, you probably don't even know the reflection, just learn what driver class to load there, God knows what to learn, the more muddled you listen).
I have many friends who go to the training institute. The training is over, and I don't know why to learn jdbc. Only a few dry cases in the class.When you go to a business interview, you're only laughed at.
Then the driver class is loaded and what DriverManager is needed to get the link.Then, write a preprocessing statement, execute it, and get the result set ResultSet.(Mom's a chicken cake. For beginners, they don't know the basic iterator, loop, just write what while (rs.next(){...} there Yes, the final result is conceivable.
I personally do not advocate going to training institutions that are not well-known or have a bad reputation. If you have to go to training, then at least find a good track record. After all, it costs so much money!If you spend money, most of your knowledge should go to the enterprise to learn from 0. Then you will be really depressed and depressed.
But there's no way, because even the worst training institutions are always more practical than schools.Every year there are so many graduates who don't know what to do after they graduate. It's really depressing.
To find a job, it is not easy to find an interview. In the past, it was training.
Then think that you really don't have any skills, so you have to bite your teeth and spend more than 10,000 yuan on training.
There are so many examples of this, and many of the blogger's friends and classmates have just taken the path of Java program development.
I don't object to going to a training institution, but I just want to say that if I have good self-control, it is better to try to study by myself.
If you really don't have self-control, going to a training institution is also a good choice.
In short, it depends on your specific situation.
OK, back to the point.
Finally, at the end of a jdbc operation, it is always necessary to close the link, otherwise it will result in a waste of resources.
Of course, these things are really important, and JDBC is how Java code accesses and manipulates databases.These basic API methods also form the root of all JDBC frameworks, such as Spring-jdbc, which encapsulates these relatively low-level code.
This is the so-called jdbc1.0 specification, but one drawback of this is that every time I do a database operation, I get a connection and close it myself.This is cumbersome and people are always smart, so in order to improve, there is a jdbc2.0 specification.
For those data sources, the concept of connection pools belongs to the jdbc2.0 specification, that is, I create a bunch of connections first and pick them up by whoever needs them.This solves the disadvantage of jdbc1.0, which does not require users to create a connection every time and then close the link.
Because this project is based on foundation, I would like to introduce how to use jdbc1.0.I will gradually encapsulate a JDBC tool class out, I have not written before, they are all directly using the framework, this is my first attempt, inevitably there will be inappropriate places, and I hope you will include more.
If you have questions or different opinions, you can point out in the comments section that I will correct them with an open mind. Thank you.
02 Preparatory knowledge
It's best to have a preliminary understanding of the following, then come back to this article. Of course, it's OK to leave it alone.
1. Reflection
2. Generics
3. Simple use of JDBC API
4. Read property files
5. IO Flow
03 Ready for mysql
Okay, let's get started.I have installed mysql locally.If mysql is installed, Baidu will be fine.
In the previous section, we created a new database Article with a user name of root and no password.The root user has the highest privileges on the local mysql.
win + R, enter cmd
Enter mysql-uroot (password not required)
Enter use Article
Well, you're done with the database.
If MySQL cannot be entered, it may not have started.On the command line, type: net start mysql.
04 Write jdbc.properties
We want to connect to the database. The most basic information is the user name, password, and database name.
Thus, we create a new config source folder in the src directory (note that the source folder is not a normal folder, all source folders do not really produce a folder, the files inside it default to CLASSPATH root directory), then create a jdbc.properties file:
content
db.username=root db.password= db.dataBaseName=article
OK, that's done, and the next step is how to read the information in this file.
05 Read properties file
Create a new test class under the test package to try to read the properties file.
package test;
/**
* Read test classes for Properties files
* @author Play a tough rabbit
*/
public class TestProperties {
public static void main(String[] args) {
}
}
The properties file is a Map-like thing, so why put the connection information in the file?That's because if you write in Java classes, that's fine.But it's not good for maintenance.
For example, once a project comes online, it is basically followed up by a dedicated maintainer. As a developer, once you change any configuration information, you just need to talk to the other party, find any properties file, and then change a line.If you write information directly in a Java class without a configuration file, it's difficult to describe it clearly and it can be particularly cumbersome to change.
That's why.
Here's the code I read, the flow of which is that I first turn this file into an input stream InputStream, then new Properties, and then load the input stream I got before.
jdbc.properties is a real file.Moreover, it is a special file because its contents are in the form of key=value.The question now is how to use Java code to read the information inside?
InputStream inputStream = TestProperties.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties p = new Properties();
try {
p.load(inputStream);
System.out.println(p);
} catch (IOException e) {
e.printStackTrace();
}You can think of InputStream as a straw
InputStream inputStream = TestProperties.class.getClassLoader().getResourceAsStream("jdbc.properties");This line of code is equivalent to inserting a straw into the jdbc.properties file to extract the information inside.
p.load(inputStream);This line of code connects the straw to the Properties object.
Well, if you really don't have a foundation for Java IO, just imagine it for a while.
System.out.println(p);Output:
{db.password=, db.dataBaseName=article, db.username=root}
Print separately:
System.out.println(p.getProperty("db.username"));
System.out.println(p.getProperty("db.password"));
System.out.println(p.getProperty("db.dataBaseName"));Output:
root
article
(The password is not shown because it is empty)
In this case, there is no problem loading the resource file.
06 Start encapsulating your own DataBaseUtils
DataBaseUtils means the database tool class, which you can think of as your own small framework.
We already know that to access the database, you need username, password, and dataBaseName.So these three data are attributes of the tool class.
private static String username; //User name
private static String password; //Password
private static String dataBaseName; //Database NameNext, define a method specifically for loading properties.
/**
* Basic information for configuring the database
* @return void
*/
public static void config(String path){
InputStream inputStream = DataBaseUtils.class.getClassLoader().getResourceAsStream(path);
Properties p = new Properties();
try {
p.load(inputStream);
username = p.getProperty("db.username");
password = p.getProperty("db.password");
dataBaseName = p.getProperty("db.dataBaseName");
} catch (IOException e) {
e.printStackTrace();
}
}Once this method is called, the private property is assigned a value.
For convenience, we let the DataBaseUtils class automatically configure jdbc.properties when it is loaded. An easy way to think of it is to define a static block and then call the config method inside:
static {
config("jdbc.properties");
}This way, the connection information will be automatically configured whenever you call the method in this DataBaseUtils.
How to get the connection:
/**
* Get Database Links
* @return Connection
*/
public static Connection getConnection(){
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+dataBaseName+"?useUnicode=true&characterEncoding=utf8",username,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}Test:
DataBaseUtils.config("jdbc.properties");
Connection conn = DataBaseUtils.getConnection();
System.out.println(conn);Result:
com.mysql.jdbc.Connection@661532
This indicates that the connection was successfully acquired.
Ways to close connections and other resources
/**
* close resource
* @param connection
* @param statement
* @param rs
*/
public static void closeConnection(Connection connection,PreparedStatement statement,ResultSet rs){
try {
if(rs!=null)rs.close();
if(statement!=null)statement.close();
if(connection!=null)connection.close();
} catch (Exception e) {
e.fillInStackTrace();
}
}07 DML Operation Implementation
DML Representation - Data manipulation language, also known as SELECT, DELETE, UPDATE, INSERT.
Now let's start wrapping the DML.
Top Code:
/**
* DML operation
* @param sql
* @param objects
*/
public static void update(String sql,Object...objects){
Connection connection = getConnection();
PreparedStatement statement = null;
try {
statement = (PreparedStatement) connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
statement.setObject(i+1, objects[i]);
}
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeConnection(connection, statement, null);
}
}Object...objects is a variable-length parameter that you can interpret as an Object array.
Test:
String id = UUID.randomUUID() + "";
String createTime = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
update("INSERT INTO t_user(id,username,password,sex,create_time,is_delete,address,telephone) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)", id,"Zhang San",123456,0,createTime,0,"secrecy","secrecy");Run result:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'id' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2983)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)
at util.DataBaseUtils.update(DataBaseUtils.java:147)
at util.DataBaseUtils.main(DataBaseUtils.java:165)If you find a mistake, don't panic and see what it says.
Because it's live, if my code says something wrong, it will put it up and correct it.I'm an ordinary person too, and it's hard to guarantee that the code will be written correctly in one go.
I know it's normal to be afraid of making mistakes when you start school.It's OK. Take your time.
Okay, let's see what it says.
Data truncation: Data too long for column 'id' at row 1Oh, that means the field length of id is too short, and the UUID we generated is too long.
Find User Class
Let's change the length of the id to varchar(100).
Next, modify TableUtils to add a table-building statement and a judgment to delete the old table.
public static String getCreateTableSQl(Class<?> clazz){
StringBuilder sb = new StringBuilder();
//Get table name
Table table = (Table) clazz.getAnnotation(Table.class);
String tableName = table.tableName();
sb.append("DROP TABLE IF EXISTS ").append(tableName).append(";\n");
sb.append("create table ");
sb.append(tableName).append("(\n");
Field[] fields = clazz.getDeclaredFields();
String primaryKey = "";
//Traverse all fields
for (int i = 0; i < fields.length; i++) {
Column column = (Column) fields[i].getAnnotations()[0];
String field = column.field();
String type = column.type();
boolean defaultNull = column.defaultNull();
sb.append("\t" + field).append(" ").append(type);
if(defaultNull){
if(type.toUpperCase().equals("TIMESTAMP")){
sb.append(",\n");
}else{
sb.append(" DEFAULT NULL,\n");
}
}else{
sb.append(" NOT NULL,\n");
if(column.primaryKey()){
primaryKey = "PRIMARY KEY ("+field+")";
}
}
}
if(!StringUtils.isEmpty(primaryKey)){
sb.append("\t").append(primaryKey);
}
sb.append("\n) DEFAULT CHARSET=utf8");
return sb.toString();
}Then the sql statement is regenerated.
String sql = TableUtils.getCreateTableSQl(User.class);
System.out.println(sql);
Add a semicolon and return.
View table structure
Successfully changed.
Okay, go back to DataBaseUtils and do the new operation
String id = UUID.randomUUID() + "";
String createTime = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
update("INSERT INTO t_user(id,username,password,sex,create_time,is_delete,address,telephone) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?)", id,"Zhang San",123456,0,createTime,0,"secrecy","secrecy");Let's check how many pieces of data are in the table now.
Well, it should have been successful.