Relevant learning recommendations: java basics

##1. JDBC connection to the database operation

You can encapsulate it in a class to simplify the code when writing.

(1) Load the JDBC-MySQL database driver

try?{
????Class.forName("com.mysql.cj.jdbc.Driver");}catch?(Exception?e){?}

(2) Connect to the database

Connection?con?=?null;//聲明要連接數(shù)據(jù)庫的對象comString?uri?=?"jdbc:mysql://localhost:3306/數(shù)據(jù)庫名?useSSL=true?&characterEncoding=utf-8";	//連接地址String?user?=?"root";	//連接數(shù)據(jù)庫的用戶名String?password?=?"123456";	//連接數(shù)據(jù)庫的密碼try?{
????con?=?DriverManager.getConnection(uri,user,password);
	//連接代碼}catch(SQLException?e){?}

(3) Write the code to operate the data in the database

2. Query operation

1, specific steps of query operation (1) Send SQL statement to the database:

Statement?sql;try{
????sql?=?con.createStatement();}catch?(SQLException?e){?}

First declare the SQL statement object , and then let the created connection object con call the method

createStatement() to create this SQL statement object. (2) Processing query results
With the SQL statement object, this object can call corresponding methods to implement various operations on the tables in the database, where the query results are stored in an object declared by the ResultSet class. That is, the SQL query statement returns a ResultSet object to the database query operation. The ResultSet is composed of data rows organized by "columns" (fields).

rs?=?sql.executeQuery("select?*?from?表的名稱");//讀取表中的所有列，*為通配符rs?=?sql.executeQuery("select?行1字段名，行2字段名...?from?表的名稱");//讀取表中特定的列

The ResultSet object can only see one row of data at a time, use the next() method to move to the next row. The ResultSet object can obtain the column value through

getXxx(int ??columnIndex) and obtain the column value by passing the column name getXxx(String columnName). (3) Close the connection

con.close();?//關(guān)閉con所連接的數(shù)據(jù)庫

Note: The database is closely bound to the connection object, and the database should be closed after use.

2, control the cursor The initial position of the cursor of the result set is in front of the first row of the result set. The result set calls the next() method to move the cursor downward (back). Returns true if the move is successful and false if the move fails.
If you want to move and display several records in the result set, you must return a scrolling result set. The method is as follows:

Statement?stmt?=?con.createStatement(int?type,int?concurrency);//獲得一個Statement對象，根據(jù)參數(shù)的值，stmt返回相應(yīng)類型的結(jié)果集：ResultSet?re?=?stmt.executeQuery(SQL語句);type的取值：決定滾動方式：
ResultSet.TYPE_FORWARD_ONLY?結(jié)果集的游標只能向下滾動
ResultSet.TYPE_SCROLL_INSENSITIVE?游標可以上下移動，數(shù)據(jù)庫變化時，結(jié)果集不變
ResultSet.TYPE_SCROLL_SENSITIVE?返回可滾動的結(jié)果集，數(shù)據(jù)變化時，結(jié)果集同步改變
Concurrency取值：決定是否可以用結(jié)果集更新數(shù)據(jù)庫
ResultSet.CONCUR_READ_ONLY?不能用結(jié)果集更新數(shù)據(jù)庫中的表
ResultSet.CONCUR_UPDATABLE?能用結(jié)果集更新數(shù)據(jù)庫中的表

滾動查詢常用的ResultSet的方法：public?boolean?previous()?將游標向上移動，當移動到結(jié)果集第一行之前時返回falsepublic?void?beforeFirst()?將游標移動到結(jié)果集的初始位置，第一行之前public?void?afterLast()?將游標移動到結(jié)果集的最后一行之后public?void?first()?將游標移動到第一行public?void?last()?將游標移動到最后一行public?boolean?isAfterLast()?判斷游標是否在最后一行之后public?boolean?isBeforeFirst()?判斷游標是否在第一行游標之前public?boolean?isFirst()?判斷游標是否指向第一行public?boolean?isLast()?判斷游標是否指向最后一行public?int?getRow()?得到當前游標所指向的行號，行號從1開始，如果結(jié)果集沒有行，返回0public?boolean?absolute(int?row)?將游標移動到參數(shù)row指定的行（參數(shù)取負數(shù)即倒數(shù)）

(3) Condition and sorting query where sub-statement:
select field from indicates where condition

（1）字段值與固定值比較
select?*?from?table?where?name='張三'（2）字段值在某個區(qū)間
select?*?from?table?where?number>5?and?number<10 and name!=&#39;李四&#39;（3）使用某些特殊的日期函數(shù)(Data:year-month-day)select * from table where year(表明日期的字段名)<1980 and month(表面日期的字段名)<=10select * from table where year(表明日期的字段名) between 2002 and 2021（4）使用某些特殊的時間函數(shù)（hour,minute,second）
select * from timelist where second(表明時間的字段名)=36（5）用操作符like進行模式匹配，使用%代替0個或多個字符，用一個下劃線_代替一個字符。
select * from table where name like&#39;%歐%&#39; //查詢name字段有“歐”字的記錄

order by statement: can be used in combination with where statement

select * from table order by height
select * from table where name like '%王%' order by name

3. Update, add and delete operations

Statement object call

public int executeUpdate(String sqlStatement);Update, add and delete records in the database table through the method specified by parameters.

（1）更新
update 表名 set 字段 = 新值 where <條件子句>（2）添加
insert?into?表?values?(對應(yīng)具體的記錄)（3）刪除
delete?from?表名?where?<條件子句>

4. Use prepared statements

Java provides a more efficient database operation mechanism, which is the PreparedStatement object, that is, the prepared statement object. Process the SQL statement into the underlying statement of the database and then transmit it to the database.

Use wildcards: When preprocessing SQL, you can use the wildcard character ? to replace the field value. Just set the specific value represented by the wildcard character before executing the prepared statement:

String?str?=?"select?*?from?表名?where?字段1?<???and?字段2?=??";PreparedStatement?sql?=?con.prepareStatement(str);sql.setXxx(int?parameterIndex,Type?x);//該方法設(shè)置?代表的值，parameterIndex代表第幾個？，x代表要替換的值。

5. General Query

Writing a general query class, the user can pass the database name and SQL statement to the object of this class, and then the object will use a two-dimensional array to return the query record.

Writing a general query class requires knowing the name and number of database table columns (fields). The common method is to use the result set ResultSet object rs to call the
getMetaData() method to return a ResultSetMetaData object:

ResultSetMetaData?metaData?=?rs.getMetaData();

Then the object metaData calls the

getColumnCount() method to return The number of columns in rs, call getColumnName(int i) to return the name of the i-th column in the result set rs.

6. Transaction

1, transaction and processing A transaction consists of a set of SQL statements. The so-called transaction processing means that the application program ensures that either all SQL statements in the transaction are executed or none of them are executed. Transaction processing is an important mechanism to ensure the integrity and consistency of data in the database.

2, JDBC transaction processing Use the setAutoCommit(boolean b) method to turn off the automatic mode:
That is to turn off the immediate effectiveness of the SQL statement, the two related operations should Change the data in the database only after all executions are completed. The method is to let the connection object call this method before obtaining the sql object:

con.setAutoCommit(false);

Use the commit() method to process the transaction:

After turning off the automatic mode, let the sql object submit multiple SQL (that is, the same transaction) statements , these statements will not take effect immediately, but will take effect until the connection object calls the method:

con.commit();

Failed to use the rollback() method to process the transaction:

That is, the operation performed by the transaction is cancelled. When con calls the commit() method for transaction processing, as long as one SQL statement is not executed successfully, the transaction execution fails and a SQLException is thrown. At this time, con must be allowed to call the rollback() method to undo all operations that cause data changes:

con.rollback();

Related free learning recommendations: mysql video tutorial

The above is the detailed content of Java program development learning JDBC and MySQL database. For more information, please follow other related articles on the PHP Chinese website!