Java program development learning JDBC and MySQL database

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;//声明要连接数据库的对象comString uri = "jdbc:mysql://localhost:3306/数据库名?useSSL=true &characterEncoding=utf-8";	//连接地址String user = "root";	//连接数据库的用户名String password = "123456";	//连接数据库的密码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(); //关闭con所连接的数据库

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对象，根据参数的值，stmt返回相应类型的结果集：ResultSet re = stmt.executeQuery(SQL语句);type的取值：决定滚动方式：
ResultSet.TYPE_FORWARD_ONLY 结果集的游标只能向下滚动
ResultSet.TYPE_SCROLL_INSENSITIVE 游标可以上下移动，数据库变化时，结果集不变
ResultSet.TYPE_SCROLL_SENSITIVE 返回可滚动的结果集，数据变化时，结果集同步改变
Concurrency取值：决定是否可以用结果集更新数据库
ResultSet.CONCUR_READ_ONLY 不能用结果集更新数据库中的表
ResultSet.CONCUR_UPDATABLE 能用结果集更新数据库中的表

滚动查询常用的ResultSet的方法：public boolean previous() 将游标向上移动，当移动到结果集第一行之前时返回falsepublic void beforeFirst() 将游标移动到结果集的初始位置，第一行之前public void afterLast() 将游标移动到结果集的最后一行之后public void first() 将游标移动到第一行public void last() 将游标移动到最后一行public boolean isAfterLast() 判断游标是否在最后一行之后public boolean isBeforeFirst() 判断游标是否在第一行游标之前public boolean isFirst() 判断游标是否指向第一行public boolean isLast() 判断游标是否指向最后一行public int getRow() 得到当前游标所指向的行号，行号从1开始，如果结果集没有行，返回0public boolean absolute(int row) 将游标移动到参数row指定的行（参数取负数即倒数）

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

（1）字段值与固定值比较
select * from table where name='张三'（2）字段值在某个区间
select * from table where number>5 and number<10 and name!=&#39;李四&#39;（3）使用某些特殊的日期函数(Data:year-month-day)select * from table where year(表明日期的字段名)<1980 and month(表面日期的字段名)<=10select * from table where year(表明日期的字段名) between 2002 and 2021（4）使用某些特殊的时间函数（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 (对应具体的记录)（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);//该方法设置?代表的值，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!