We completed the common addition, deletion and modification operations for operating the database in the previous article. Now in this article we will implement the operation of querying the database.
Take the customers table as an example
Examples of query operations
The operation of querying the database returns an additional result set compared to the operation of adding, deleting, and modifying, so we cannot directly use the execute() method in the program. First I want to write a query using the customer table in my database as an example. This time we use the ResultSet class to receive our result set.
public class CustomerForQuery {
@Test
public void testQuery() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet resultSet=null;
try {
conn = jdbcutils.getConnection();
String sql="select id,name,email,birth from customers where id =?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
//Execute and return the result set
resultSet = ps.executeQuery();
//Process the result set
if(resultSet.next()) {//Determine whether the first row of the result set has data, if so, return true and move the pointer down
//Get each field value of the current data
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
//Method 1: Direct display
//System.out.println("id=" + id + " name=" + name + " email=" + email + " birth=" + birth);
\t\t\t\t
//Method 2: Encapsulate into an array
//Object[] data=new Object[] {id,name,email,birth};
\t\t\t\t
//Method 3: Encapsulate the data into an object
Customer customer=new Customer(id,name,email,birth);
System.out.println(customer);
\t\t\t\t\t
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
jdbcutils.closeResource(conn, ps, resultSet);
}
\t\t
}
}
Displaying our data can be output directly or explicitly, or it can be encapsulated into an array. Of course, I chose to use a class to specifically encapsulate it. Insist that a data table corresponds to a Java class, a record in the table corresponds to an object of the Java class, and a field in the table corresponds to an attribute of the Java class. So I chose to create a Customer class:
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
super();
// TODO Auto-generated constructor stub
}
public Customer(int id, String name, String email, Date birth) {
super();
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
@Override
public String toString() {
return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + ", getClass()="
+ getClass() + ", hashCode()=" + hashCode() + ", toString()=" + super.toString() + "]";
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
\t
}
This is the source of the Customer class in the third method in the above code.
General query operations (for customers table)
To implement a universal query operation on the customers table, we need to solve the problem of unknown number of query data. First, solve the number of placeholders. You can use Object …arges to accept the sql statement. By getting the length of args, we can get the number of placeholders that need to be filled. What about the number of data types in the result set? This requires the introduction of the ResultSetMetaData class. We can use its packaging method to complete operations such as obtaining the number of columns and column names in the result set. Finally, the data with the specified name in the result set is obtained through reflection. Considering that the data may be private, it is first allowed to be accessed through the setAccessible() method, and finally the value is assigned to the Customer object. Here is the code:
public Customer queryForCustomers(String sql,Object ...arges) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = jdbcutils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<arges.length;i + + ) {
ps.setObject(i + 1, arges[i]);
}
rs = ps.executeQuery();
//Get the metadata of the result set
ResultSetMetaData rsmd = rs.getMetaData();
//Get the data in the result set through ResultSetMetData
int columnCount = rsmd.getColumnCount();
while(rs.next()) {
Customer cust=new Customer();
//Process each column in a row of data in the result set
for(int i=0;i<columnCount;i + + ) {
Object columnValue = rs.getObject(i + 1);
//Get the column name of each column
String columnName = rsmd.getColumnName(i + 1);
//You need to assign a value to a certain attribute of cust through reflection
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(cust, columnValue);
}
return cust;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
jdbcutils.closeResource(conn, ps, rs);
}
return null;
}
There is the following successful test code:
@Test
public void testQueryForCustomer() {
String sql="select id,name,birth,email from customers where id =?";
String sql2="select name,birth,email from customers where name= ?";
Customer customer = queryForCustomers(sql2, "Faye Wong");
System.out.println(customer);
}
Take the Order table as an example
Examples of query operations
Same as above, with code
@Test
public void testQuery1() {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = jdbcutils.getConnection();
String sql="select order_id,order_name,order_date from `order` where order_id=?";
ps = conn.prepareStatement(sql);
ps.setObject(1, 1);
rs = ps.executeQuery();
if(rs.next()) {
int id = (int)rs.getObject(1);
String name=(String) rs.getObject(2);
Date date=(Date) rs.getObject(3);
Order order = new Order(id,name,date);
System.out.println(order);
}
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
jdbcutils.closeResource(conn, ps, rs);
}
\t\t
}
It worked successfully, not much to say.
General query operation (for Order table)
Next, with the example of a general query for the customers table in front, we can write the code
//General operations for the Order table
public Order OrderForQuery(String sql,Object ...args){
Connection conn=null;
PreparedStatement ps=null;
//Execute to obtain the result set
ResultSet rs=null;
try {
conn = jdbcutils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i + + ) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//Get the metadata of the result set
ResultSetMetaData rsmd = rs.getMetaData();
//Get the number of columns
int columnCount = rsmd.getColumnCount();
if(rs.next()) {
Order order=new Order();
for(int i=0;i<columnCount;i + + ) {
//Get the column value of each column
Object columnValue = rs.getObject(i + 1);
//Get the column name of the column, you need to get the alias of the class
//String columnName = rsmd.getColumnName(i + 1); (deprecated)
String columnName=rsmd.getColumnLabel(i + 1);
//Assign the attribute value of the specified name of the object to the specified value through reflection
Field field = Order.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
jdbcutils.closeResource(conn, ps, rs);
}
return null;
}
There is also the Order class to accept the result set:
public class Order {
private int orderid;
private String ordername;
private Date orderdate;
public Order() {
super();
}
public Order(int orderid, String ordername, Date orderdate) {
super();
this.orderid = orderid;
this.ordername = ordername;
this.orderdate = orderdate;
}
@Override
public String toString() {
return "Order [orderid=" + orderid + ", ordername=" + ordername + ", orderdate=" + orderdate + "]";
}
public int getOrderid() {
return orderid;
}
public void setOrderid(int orderid) {
this.orderid = orderid;
}
public String getOrdername() {
return ordername;
}
public void setOrdername(String ordername) {
this.ordername = ordername;
}
public Date getOrderdate() {
return orderdate;
}
public void setOrderdate(Date orderdate) {
this.orderdate = orderdate;
}
\t
}
It should be noted that the general query of the order table is different in some details from the customer query. The code where I will get the column name of the column
String columnName = rsmd.getColumnName(i + 1);
replaced by
String columnName=rsmd.getColumnLabel(i + 1);
The specific reason is that the field names of the table are different from the attribute names of my class. This is a situation that has not been encountered in the general query method of writing the customers table. I chose to use the getColumnLable() method to replace the old method, and alias the column name in the sql statement (the alias is the same as the class attribute name), so that the method can recognize the alias of the class. If the program does not replace the old method, it will report an error java.lang.NoSuchFieldException, which means that the name is not found. When getColumnLable() learns that there is no alias for the field in SQL, it will get the column name of the field. This is why I recommend replacing the original method.
General table query
With the previous two tables as a foundation, we can start to try to write a query code for a general table. Now there is a problem in front of us. In the code writing of the previous two tables, we directly created the classes and objects of the tables based on the specific conditions of the tables. Now we can’t do this because we can’t determine the call to the table. Which table does the method need to operate on? The solution is to use the knowledge of generics and pass in the required table type in the method. Of course, the corresponding class must have been implemented and created.
public <T> List<T> getForList(Class<T> clazz,String sql,Object ...args){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn = jdbcutils.getConnection();
ps = conn.prepareStatement(sql);
for(int i=0;i<args.length;i + + ) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
//Get the metadata of the result set
ResultSetMetaData rsmd = rs.getMetaData();
//Get the data in the result set through ResultSetMetData
int columnCount = rsmd.getColumnCount();
//Create collection object
ArrayList<T> list=new ArrayList<>();
while(rs.next()) {
T t = clazz.newInstance();
//Process each column in a row of data in the result set: assign a value to the attribute specified by the t object
for(int i=0;i<columnCount;i + + ) {
Object columnValue = rs.getObject(i + 1);
//Get the column name of each column
String columnName = rsmd.getColumnLabel(i + 1);
//You need to assign a value to a certain attribute of cust through reflection
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
jdbcutils.closeResource(conn, ps, rs);
}
return null;
}
If you observe the code carefully, you will find that I use a List collection to receive multiple objects, because in actual application, we definitely need more than one piece of data, and we must operate on multiple data, and arrays are not suitable for the current application. , so I used the List collection.
There is the following test code, which runs successfully.
@Test
public void testGetForList() {
String sql="select id,name,email from customers where id<?";
List<Customer> list=getForList(Customer.class, sql, 12);
list.forEach(System.out::println);
}
In fact, most of the operations have been completed in the first two cases. The third general table query is more of a summary, transforming the determined into customizable, all of which are detailed changes.