I queried multiple rows of data yesterday, and I will improve it today
First, write a class that queries a row in the tool class JdbcUtil, and use generics to pass parameters:
public static <T> T selectRow(String sql, Class<T> c) {
try {
//1. Register the driver-radioload com.mysql.jdbc in the jar package
Class.forName("com.mysql.jdbc.Driver");
// get connection object
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/summer-camp2023?characterEncoding=utf8", "root", "root");
//3. Define sql
//4. Need to create statemrnt
Statement statement = con. createStatement();
//5.statement executes sql and returns the result set
ResultSet rs = statement. executeQuery(sql);
ResultSetMetaData md = rs. getMetaData();
int columnCount = md. getColumnCount();
//6. Analysis
if (rs.next()) {//The cursor to read the result set moves down, which row the cursor is by default, and the row where the column name is located
T t = c. newInstance();
for (int i = 1; i <= columnCount; i ++ ) {
//Get the value of each column by the serial number of the column
Object value = rs. getObject(i);
if (value != null) {
//Through the new sequence, get the column name of each column
String columnName = md. getColumnName(i);
//Because the column name is consistent with the attribute name in the entity class t, construct a reflective set method for each attribute
Field f = c. getDeclaredField(columnName);
// Assign private attributes
f. setAccessible(true);
//Using reflection, give value to the property of object t
f. set(t, value);
}
}
return t;
}
//7. Close the resource
statement. close();
con. close();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
The same method queries a column:
public static <T> List<T> selectColumn(String sql, Class<T> c) {
List<T> tList = new ArrayList<>();
try {
//1. Register the driver-radioload com.mysql.jdbc in the jar package
Class.forName("com.mysql.jdbc.Driver");
// get connection object
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/summer-camp2023?characterEncoding=utf8", "root", "root");
//3. Define sql
//4. Need to create statemrnt
Statement statement = con. createStatement();
//5.statement executes sql and returns the result set
ResultSet rs = statement. executeQuery(sql);
ResultSetMetaData md = rs. getMetaData();
int columnCount = md. getColumnCount();
//6. Analysis
while (rs.next()) {//The cursor to read the result set moves down, which row the cursor is by default, and the row where the column name is located
//Get the value of each column by the serial number of the column
T t = (T) rs. getObject(1);
tList. add(t);
}
//7. Close the resource
statement. close();
con. close();
} catch (Exception e) {
e.printStackTrace();
}
return tList;
Query a single:
public static <T> T selectOne(String sql, Class<T> c) {
List<T> tList = new ArrayList<>();
try {
//1. Register the driver-radioload com.mysql.jdbc in the jar package
Class.forName("com.mysql.jdbc.Driver");
// get connection object
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/summer-camp2023?characterEncoding=utf8", "root", "root");
//3. Define sql
//4. Need to create statemrnt
Statement statement = con. createStatement();
//5.statement executes sql and returns the result set
ResultSet rs = statement. executeQuery(sql);
ResultSetMetaData md = rs. getMetaData();
int columnCount = md. getColumnCount();
//6. Analysis
T t=null;
if(rs. next()){
t = (T) rs. getObject(1);
}
//7. Close the resource
statement. close();
con. close();
return t;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
And the method of addition, deletion and modification:
public static int update(String sql) {
try {
//1. Register the driver-radioload com.mysql.jdbc in the jar package
Class.forName("com.mysql.jdbc.Driver");
// get connection object
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/summer-camp2023?characterEncoding=utf8", "root", "root");
//3. Define sql
//4. Need to create statemrnt
Statement statement = con. createStatement();
//5.statement executes sql and returns the result set
int i = statement. executeUpdate(sql);
//7. Close the resource
statement. close();
con. close();
return i;
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
Create two login methods in Mysql:
Then create the JdbcUtilPlus class in util, copy and modify the code in JdbcUtil:
Write a Test test method:
Output login failure and success:
JSON (JavaScript Object Notation) is a lightweight data interchange format used to transmit and store data in a way that is easy to read and write. It is a text format and is language independent so data exchange between different programming languages is possible.
JSON represents data in the form of key-value pairs. A JSON object consists of one or more key-value pairs, and each key-value pair consists of a property name and a corresponding value. Values can be strings, numbers, booleans, arrays, objects, or null.
Write and configure the environment before writing:
The configuration was successful:
Create a new Servlet-7.11 project, and copy a copy of jdbc-7.10:
Then import the package:
Create dept and vue to write data interaction code with myql and java:
Modify the existing code and add a Json class:
package com.hp.utils;
import com.alibaba.fastjson.JSON;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
public class JsonUtil {
public static void transJason(Object obj, HttpServletResponse resp){
try {
//Convert the data into json format, and then respond to the browser
String json_string = JSON.toJSONString(obj);
resp.setContentType("application/json;charset=utf-8");
resp.getWriter().write(json_string);
}catch (Exception e){
e.printStackTrace();
}
}
}
write in dept:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<script src="/vue/vue.min.js"></script>
<script src="/vue/axios.min.js"></script>
</head>
<body>
<div id="app">
<button @click="showDept">Click to display data</button>
<table border="1" cellpadding="20" cellspacing="0">
<tr>Serial number</tr>
<tr>Department name</tr>
<tr>Department location</tr>
<tr>Department leader</tr>
<tr v-for="dept in deptList">
<td>{<!-- -->{dept.did}}</td>
<td>{<!-- -->{dept.dname}}</td>
<td>{<!-- -->{dept.dlocation}}</td>
<td>{<!-- -->{dept.leader}}</td>
</tr>
</table>
</div>
</body>
<script>
new Vue({
el:'#app',
data: {
deptList:[]
},
methods: {
showDept(){
axios.get('http://localhost:8080/deptC').then((resp)=>{
console. log(resp, resp. data)
this.deptList=resp.data
})
}
}
})
</script>
</html>
Connect with the server:
If the following is displayed, the connection is successful:
Finally, there is this sub:
That’s all for today, let’s continue to carve the boat and seek the sword. . . . . .