Preface: What is JDBC
Java Database Connectivity (JDBC) is an application program interface used in the Java language to standardize how client programs access databases, providing methods such as querying and updating data in the database. JDBC is also a trademark of Sun Microsystems. It is JDBC for relational databases.
Simply put, it is a Java API used to execute SQL statements. Through JDBC, we can directly use Java programming to operate on relational databases. Through encapsulation, developers can use pure Java APIs to complete SQL execution.
1. Preparation work (I): MySQL installation configuration and basic learning
Before using JDBC to operate a database, first you need to have a database. Here are 3 links for readers to learn by themselves. If you have had experience using SQL language (including classroom learning in school), the first two links are more than enough.
Mysql download, installation, deployment and graphical detailed operation tutorial://www.VeVB.COM/article/87690.htm
It is recommended to read the introductory tutorial while practicing, while practicing basic operations such as insert, update, select, delete, etc., and build the table you want to use later.
The following figure is the table of the database I will use for the next demonstration.
2. Preparation work (2): Download the corresponding jar package of the database and import it
Using JDBC requires importing the corresponding jar package in the project. The correspondence between the database and the JDBC package can refer to the jar package, driver class name and URL format corresponding to various databases. Import method under Eclipse:
Right-click on the project icon, select "Properties", select "Add External JARs..." in "Java Bulid Path", and select the jar package you obtained after downloading and decompressing.
If you operate on MySQL, the following import will not report an error:
import com.mysql.jdbc.Connection; import com.mysql.jdbc.PreparedStatement;
In addition, you also need JDBC packages, just import them directly.
import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;
3. Basic operations of JDBC
For simplicity, the operations, commands, and parameters related to the database are hardcoded. Interested readers can explore these to reduce the coupling between data and operations.
Let’s first look at the specific code and practice. The fifth part of this article has conducted a brief study on the API used.
All methods and data members below are inside the public class JDBCOperation.
(1) Define the record class (optional)
This is mainly done to facilitate operation and interface definition, and is not necessary.
static class Student { private String Id; private String Name; private String Sex; private String Age; Student(String Name, String Sex, String Age) { this.Id = null; //default this.Name = Name; this.Sex = Sex; this.Age = Age; } public String getId() { return Id; } public void setId(String Id) { this.Id = Id; } public String getName() { return Name; } public void setName(String Name) { this.Name = Name; } public String getSex() { return Sex; } public void setSex(String Sex) { this.Sex = Sex; } public String getAge() { return Age; } public void setage(String Age) { this.Age = Age; }}(2) Retrieval of connection
The connection to the database must be obtained before the operation.
private static Connection getConn() { String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/samp_db"; String username = "root"; String password = ""; Connection conn = null; try { Class.forName(driver); //classLoader, load the corresponding driver conn = (Connection) DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn;}(3)insert
private static int insert(Student student) { Connection conn = getConn(); int i = 0; String sql = "insert into students (Name,Sex,Age) values(?,?,?)"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); pstmt.setString(1, student.getName()); pstmt.setString(2, student.getSex()); pstmt.setString(3, student.getAge()); i = pstmt.executeUpdate(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i;}(4) update
private static int update(Student student) { Connection conn = getConn(); int i = 0; String sql = "update students set Age='" + student.getAge() + "' where Name='" + student.getName() + "'"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i;}(5)select
Take select * from XXX as an example.
private static Integer getAll() { Connection conn = getConn(); String sql = "select * from students"; PreparedStatement pstmt; try { pstmt = (PreparedStatement)conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); int col = rs.getMetaData().getColumnCount(); System.out.println("============================"); while (rs.next()) { for (int i = 1; i <= col; i++) { System.out.print(rs.getString(i) + "/t"); if ((i == 2) && (rs.getString(i).length() < 8)) { System.out.print("/t"); } } System.out.println(""); } System.out.println("============================"); } catch (SQLException e) { e.printStackTrace(); } return null;}(6) delete
private static int delete(String name) { Connection conn = getConn(); int i = 0; String sql = "delete from students where Name='" + name + "'"; PreparedStatement pstmt; try { pstmt = (PreparedStatement) conn.prepareStatement(sql); i = pstmt.executeUpdate(); System.out.println("resutl: " + i); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } return i;}4. Test
Before testing, the corresponding database service needs to be opened in the system. MySQL startup command under Windows is net start mysql
Test code
public static void main(String args[]) { JDBCOperation.getAll(); JDBCOperation.insert(new Student("Achilles", "Male", "14")); JDBCOperation.getAll(); JDBCOperation.update(new Student("Bean", "", "7")); JDBCOperation.delete("Achilles"); JDBCOperation.getAll();}Output in Eclipse
============================1 Ender male 8 2 Bean male 6 3 Petra fema 9 4 Peter male 9 5 _Graff male 40 6 GOD fema 255 ========================================================1 Ender male 8 2 Bean male 6 3 Petra fema 9 4 Peter male 9 5 _Graff male 40 6 GOD fema 255 7 Achilles Male 14 ============================resutl: 1resutl: 1============================1 Ender male 8 2 Bean male 7 3 Petra fema 9 4 Peter male 9 5 _Graff male 40 6 GOD fema 255 ============================
V. Code analysis
In the above process of adding, deleting, modifying and searching the database, it can be found in common, that is, a common process:
(1) Create Connection object and SQL query command string;
(2) Pass the SQL query command to the Connection object and obtain the PreparedStatement object;
(3) Execute executeUpdate() or executeQurey() on the PreparedStatement object to obtain the result;
(4) Close the PreparedStatement object and the Connection object one after another.
It can be seen that when using JDBC, the most common types of contact are Connection and PreparedStatement, and the ResultSet class in select.
Connection
java.sql
Connection
All Super Interfaces: Wrapper
--------------------------------------------------------------------------------
public interface Connectionextends Wrapper
Connection (session) to a specific database. Execute SQL statements in the connection context and return the result.
The database of the Connection object can provide information describing its tables, supported SQL syntax, stored procedures, this connection function, and so on. This information is obtained using the getMetaData method.
PreparedStatemnt
java.sql
Interface PreparedStatement
All super interfaces: Statement, Wrapper All known sub-interfaces: CallableStatement
--------------------------------------------------------------------------------
public interface PreparedStatementextends Statement
An object that represents a precompiled SQL statement.
SQL statements are precompiled and stored in PreparedStatement objects. This statement can then be executed efficiently multiple times using this object.
Common methods
boolean execute()
Executes a SQL statement in this PreparedStatement object, which can be any kind of SQL statement.
ResultSet executeQuery()
Executes a SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.
int executeUpdate()
Execute SQL statements in this PreparedStatement object, which must be a SQL Data Manipulation Language (DML) statement, such as an INSERT, UPDATE, or DELETE statement, or an SQL statement without return content, such as a DDL statement.
ResultsSet
java.sql
Interface ResultSet
All super interfaces: Wrapper all known sub-interfaces: CachedRowSet, FilteredRowSet, JdbcRowSet, JoinRowSet, RowSet, SyncResolver, WebRowSet
--------------------------------------------------------------------------------
public interface ResultSetextends Wrapper
A data table representing a database result set is usually generated by executing a statement querying the database.
6. Think about problems
1. Each SQL operation requires establishing and closing the connection, which will inevitably consume a lot of resource overhead. How to avoid it?
Analysis: Connection pooling can be used to perform unified maintenance of connections without having to establish and close them every time. In fact, this is what many tools for encapsulating JDBC are used.
2. What should I do if the format of the incoming data is different from the database definition in Java code? For example, assign Java String object to the tinyint attribute of the database.
Analysis: When executing SQL statements, the database will attempt to convert. According to my experiment, if you pass the age attribute of tinyint with a pure letter using a String object, it will be converted to 0. The specific conversion rules should be related to the database.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.