1. Use JDBC to query all data of database table t_user
1.User table
Name Data type Primary key Whether it is empty Description
ID number is the user number
NAME Varchar2(50) username
AGE varchar2(5) user age
BIRTH date User birthday
PWD varchar2(20) No User password
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcMysqlTest {<!-- -->
public static void main(String[] args) throws
ClassNotFoundException,SQLException {<!-- -->
//1. Load jdbc driver
Class.forName("com.mysql.jdbc.Driver");
//2. Define the connection url
String url ="jdbc:mysql://127.0.0.1:3306/neuedu";
//3. Get the database connection object
Connection conn =DriverManager.getConnection(url,"root","root");
//4. Obtain the statement object (used to execute sql statements and return results)
Statement st =conn.createStatement();
//5. Execute query or update
String sql ="select id,name,age,birth from t_user";
ResultSet rs = st.executeQuery(sql);
//6. Process the results (traverse to obtain all the queried data)
while(rs.next()){<!-- -->
int id =rs.getInt("id");
String name =rs.getString("name");
String age =rs.getString("age");
Date birth =rs.getDate("birth");
System.out.println(id + ":" + name + ":" + age + ":" + birth);
}
//7. Close the connection (release resources)
rs.close();
st.close();
conn.close();
}
}
The results of running the program are as follows:
7:zhangsan:age:2015-09-01
8:lisi:24:2015-09-01
9:wangwu:25:2015-09-01
10:wang:23:2015-09-01
The above gives you a rough introduction to the commonly used related classes and interfaces involved in JDBC. The introduction of the methods contained in each class and interface is not very comprehensive. I hope you can make full use of the Java API tool in the subsequent learning process. , and constantly improve your learning ability.
2.DBUtil class
[Example 7-2] The DBUtil class that encapsulates opening connections and closing resources.
Usually, whether you are querying data or performing addition, deletion and modification operations, you need to open connections, close resources and other operations. Therefore, you can encapsulate the opening and closing of connections into a tool class. All examples later in this chapter use the same connection for data access. The following DBUtil class encapsulates the open and close connection methods.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {<!-- -->
//This code completes loading the database driver. The entire program only needs to be loaded once, so it is placed in a static block.
static{<!-- -->
try {<!-- -->
Class.forName("com.mysql.jdbc.Driver");//oracle database driver
} catch (ClassNotFoundException e) {<!-- -->
e.printStackTrace();
}
}
//Get database connection method
public static Connection getConnection() throws SQLException{<!-- -->
String url ="jdbc:mysql://127.0.0.1:3306/neuedu";
Connection conn = DriverManager.getConnection(url,"root","root");
return conn;
}
//Release resources
public static void close (Statement st, Connection conn) {<!-- -->
try{<!-- -->
if(st !=null){<!-- -->
try {<!-- -->
st.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}finally{<!-- -->
if(conn !=null){<!-- -->
try {<!-- -->
conn.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
}
public static void close(ResultSet rs, Statement st, Connection conn) {<!-- -->
try {<!-- -->
if (rs !=null) {<!-- -->
try {<!-- -->
rs.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}finally{<!-- -->
try {<!-- -->
if (st !=null) {<!-- -->
try {<!-- -->
st.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}finally{<!-- -->
if (conn !=null) {<!-- -->
try {<!-- -->
conn.close();
} catch (SQLException e) {<!-- -->
e.printStackTrace();
}
}
}
}
}
}
Use the DBUtil class to operate the database
[Example 7-3] Using the DBUtil class to operate the database
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
public class UserDao {<!-- -->
//Query all data in the t_user table
public static void query() throws ClassNotFoundException, SQLException{<!-- -->
Connection conn =null;
Statement?st?=?null;
ResultSet rs=null;
try{<!-- -->
conn = DBUtil.getConnection();//Directly call the DBUtil class’s method to obtain the database connection
String sql ="select id,name,age,birth from t_user";
st =conn.createStatement();
rs =st.executeQuery(sql);
while(rs.next()){<!-- -->
String id =rs.getString(1);
String name =rs.getString(2);
int age =rs.getInt(3);
Timestamp ts= rs.getTimestamp("birth");
//Format ts
SimpleDateFormat sdf =new SimpleDateFormat("Yyyy year MM month dd day HH:mm:ss");
String birth = sdf.format(ts);
System.out.println(id + " $ " + name + " $ " + age + " $ " + birth);
}
}finally{<!-- -->
DBUtil.close(rs, St, conn); //Call the DBUtil method to release resources.
}
}
//Call the query operation in the main() method.
public static void main(String[] args) throws ClassNotFoundException, SQLException {<!-- -->
query();
}
}
Analysis of running results:
Call the query() method to output the result:
7
?
z
h
a
n
g
s
a
n
?
zhangsan
zhangsan 23
?
2015
Year
09
moon
01
day
15
:
15
:
068
?
September 1, 2015 15:15:06 8
September 1, 2015 15:15:068 lisi
?
twenty four
?
twenty four
24 September 1, 2015 15:15:23
9
?
w
a
n
g
w
u
?
wangwu
wangwu 25
?
2015
Year
09
moon
01
day
15
:
15
:
5210
?
September 1, 2015 15:15:52 10
September 1, 2015 15:15:5210 hello1
?
500
?
500
500 September 1, 2015 15:16:03
Three.PreparedStatement
PreparedStatement object represents the object of precompiled SQL statement. In order to solve the SQL injection problem caused by static splicing of Statement, the PreparedStatement interface is introduced. The PreparedStatement interface is a sub-interface of the Statement interface, allowing the same SQL statement to be executed multiple times using different parameters. The Connection interface provides methods for creating PreparedStatement objects, which can specify SQL statements:
PreparedStatement prepareStatement(String sql) throws SQLException
The PreparedStatement object inherits Statement, but the PreparedStatement statement contains warning precompiled SQL statements, so it can achieve higher execution efficiency. Although Statement can be used to operate the database, it is only suitable for simple SQL statements. If we need to execute a SQL statement with parameters, we must use the PreparedStatement class object. PreparedStatement objects are used to execute precompiled SQL statements with or without input parameters. The statements can contain multiple fields represented by question marks “?”. In the program, the setXxx() method can be used to set the content of this field, thereby enhancing The dynamics of programming.
PreparedStatement, like the Statement object, provides many basic database operation methods. Listed below are three methods for executing SQL commands.
(1) ResultSetexecuteQuery(): You can execute SQL queries and obtain ResultSet objects
(2) int executeUpdate(): You can perform Update/Insert/Delete operations, and the return value is the number of rows affected by the operation.
(3) boolean execute(): This is the most general execution method. It can execute any SQL statement and then obtain a Boolean value indicating whether to return a ResultSet.
[Example 7-5] Use PreparedStatement to solve the SQL injection problem of the login function in Example 7-4.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlInject {<!-- -->
public static void login(String name, String PWD) throws SQLException{<!-- -->
Connection conn =null;
PreparedStatement ps=null;
ResultSet rs=null;
try{<!-- -->
conn = DBUtil.getConnection();
String sql ="SELECT *FROM t_user WHERE NAME=?AND PWD=?";
ps =conn.prepareStatement(sql);
//Setting parameters
ps.setString(1, id);
ps.setString(2, name);
rs = ps.executeQuery();
if(rs.next()){<!-- -->
System.out.println("Login successful..");
}else{<!-- -->
System.out.println("Login failed..");
}
}finally{<!-- -->
DBUtil.close(rs, ps, conn);
}
}
public static void main(String[] args) throws SQLException {<!-- -->
login("123123", "sadfsdf' or 1=1 or ''='");//Solving SQL injection
}
}
Use PreparedStatement to implement addition, deletion, modification and query operations on user tables.
[Example 7-6] Use PreparedStatement to implement add, delete, modify and query operations on user tables.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
public class UserDaoPreparedStatement {<!-- -->
//Insert operation
public static void insert(String name, int age, Date birth)
throws SQLException {<!-- -->
Connection conn =null;
PreparedStatement ps=null;
try {<!-- -->
conn = DBUtil.getConnection();
String sql ="insert into t_user values(seq_user.nextval,?,?,?)";
ps =conn.prepareStatement(sql);
//Set parameters, how many are there? How many parameter values need to be set?
ps.setString(1, name);
ps.setInt(2, age);
ps.setTimestamp(3, newTimestamp(birth.getTime()));
intresult=ps.executeUpdate();
if (result >0) {<!-- -->
System.out.println("insert Success...");
} else {<!-- -->
System.out.println("insert failed...");
}
}finally{<!-- -->
DBUtil.close(ps, conn);
}
}
public static void main(String[] args) throws SQLException {<!-- -->
insert("hello", 234, new Date());
}
}