Java connection and Database Control Summary (JDBC), javajdbc

Java connection and Database Control Summary (JDBC), javajdbc

Seeing the database connection reminds me of the miserable experience of participating in the Team assessment at the end of my freshman year ~~, I still remember logging on to Eclipse by following the code in the book, and then reporting all kinds of errors... After an error is reported, it is silly to check the code of the book over and over again. After finding that the code is correct, it is particularly difficult-how can I report the error like a book? Finally, I learned from Google that I needed to add a driver package, which would be wasted for many hours ~~ At the beginning, JDBC and JDBC Driver were not well differentiated, and the past was hard to look back...

At the end of my sophomore year, I held the mentality of "I was defended against myself, but I also pitted my younger brother and let them experience the pitfalls." I did not send a driver package to them when I checked my younger brother, I heard from them later that it took them a few days...

At the end of my junior year, it was my turn to evaluate my freshman year. Then my sophomore year and my freshman year said, "A driver package is required for database connection. You can learn about and download it yourself, if you do not want to solve the problem, you must learn to solve the problem. My brother did not provide me directly, and it took me a few days. So this year I have done a good job and remind you to pay attention to it ...", When I heard this, I couldn't help but feel bad about it... (^ _ ^, This does not blame me. If you have any questions that cannot be solved, you can ask your brother. If you haven't asked me, you just need to escape ~~)

Now, the memories are over ~~. To miss the previous "ignorance of Youth", I would like to summarize the Java database connection and JDBC Driver.

JDBC and JDBC Driver

First, you must understand what isJDBC, What isJDBC Driver:

JDBC Driver

JDBC Driver is generally provided by the corresponding database. For example, MySQL provides ctor/J Driver (according to official instructions, Connector/J belongs to 4th types of drivers. For more details about driver types, see wikipedia ).

JDBC Driver Installation

The installation of JDBC Driver is very simple. You only need to go to the official website to download the Driver provided by it, and put the corresponding Jar package (usually calledMysql-connector-java-version-bin.jarIn Java Classpath. If you are using J2EE like me, you can directly put it inWEB-INF/libDirectory, or use tools like Maven to add. For details, see the official documentation.

Use

The use of JDBC Driver has many examples, so it is no longer cumbersome. After all, it is just a driver. We use JDBC APIs to call this driver to deal with databases. For more usage summary, see the following section.

JDBC API

JDBC is only some interfaces defined in Java, and it is also part of JDK. Just like common interfaces such as files, we only need to call it to complete the purpose. Since it is an API, we have to go to the documentation to get familiar with it for better use. These classes are in java. SQL and javax. SQL respectively.

Connection

To use JDBC APIs to manipulate databases, you must first connect to the database. There are two ways to connect to a database:

• Use DriverManager. When DriverManager is used to establish a connection to the database for the first time, it will automaticallyClass pathTo find and load the JDBC 4.0 driver.Note thatIf it is a version earlier than 4.0, You need to manually load it.
• Use DataSource. According to official recommendations, DataSource should be used first. Compared with the simple DriverManager, it is complex, comprehensive, and detailed.

Use DriverManager to connect to the database:
To use DriverManager, we must first load the class and use the simplest method (Add the following code to the class to be loaded)

Class.forName("com.mysql.jdbc.Driver").newInstance();

After DriverManager is loaded, we can use it to obtain the connection to the database (assuming we use local data, the default path is localhost; the database name is test; the username is root; and the password is 123):

// The DriverManager and Connection classes are defined in java. SQL as String URL = "jdbc: mysql: // localhost: 3306/test? User = root & password = 123 "; Connection conn = DriverManager. getConnection (URL); // or String URL = "jdbc: mysql: // localhost: 3306/test"; String user = "root"; String password = "123 "; connection conn = DriverManager. getConnection (URL, user, password); // or String URL = "jdbc: mysql: // localhost: 3306/test"; Properties connectionProps = new Properties (); connectionProps. put ("user", this. userName); connectionProps. put ("password", this. password); Connection conn = DriverManager. getConnection (URL, connectionProps );

(In my opinion, the first one looks simple and convenient, but it is difficult to modify and has low readability. It is not recommended. If you only want to pass the user name and password, you can choose the second one. If you have many parameters to pass, such as character encoding and user name and password, the third type is preferred .)

To connect to the database, we only need to call the getConnection () method and return the Connection to the database. With this Connection, we can operate on the data.

Note that the string parameter URL in the getConnection method. This string specifiesDatabase path, database name, database configuration (username and password ).The URL Syntax of MySQL is as follows:

Jdbc: mysql: // [host] [, failoverhost...] [: port]/[database] [? PropertyName1] [= propertyValue1] [& propertyName2] [= propertyValue2]...
Detailed description

Connect using DataSource:
DataSource connection involves a lot of things, and you can find another time to summarize it. Pai_^

Create statement

After connecting to the database, I can control the database. We usually write SQL statements to manipulate the database. Therefore, before operating the database, we need to create SQL statements. JDBC defines three types of statement interfaces to run SQL statements and return execution results:

• Statement
• PreparedStatement
• CallableStatement

Correspondingly, there are three methods to create a statement:

• Connection.createStatement()-- Create a common statement, which usually does not need to provide parameters. More
• Connection.prepareStatement(String stringSQL)-- Create a pre-compiled statement. A string SQL statement with a placeholder is usually provided. More
• Connection.prepareCall()-- Create a stored procedure. More

Note that,StatementAndPreparedStatement. Their major differences are:

Database Control

The most common and simple way to manipulate databases isselect,update,delete,insertAnd other common operations. So how do we implement these behaviors through the jdbc api?

Generally, we useStatement.executeQuery(String sql)To executeselectQuery operation. It returns a ResultSet object that contains the data returned by the query.update,delete,insertAnd other update operationsStatement.executeUpdate(String sql)It returns an integer representing the number of affected rows.

ResultSet

The ResultSet is a dataset returned when a database is queried. We can think of it as a data table similar to a data table, just as the data table returned by the console when we run the select statement using the command line. The difference is that it has a pointer to the first row of data at the beginning. Then we can callResultSet.next()To move the pointer to get the data of each row. This function returns the result when the pointer moves to the next row of the last row.falseWe can use this feature to traverse the entire table as the termination condition. (The first row and the next row in the last row are non-existent rows and virtual rows)

In addition, the ResultSet has 3 medium types (more ):

To specify the type of the ResultSet, we only need to specify the following when creating the Statement:

 Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE);

OK, I can get any row in the data table. Now we can get the data of a specific column in a row of data. ResultSet providesGetterMethods (getBoolean, getLong, and getInt) to obtain the columns of the current row. The parameters of these methods can be either the index number of a column (an integer starting from 1) or the column alias or column name (string ). For example, if ResultSet. getInt (1) is used to obtain the data in the first column, ResultSet. getFloat ("score") is used to obtain the column named score.