前端
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<script src='js/jQuery.js' ></script>
<script src='js/index_1.js' defer></script>
</head>
<body>
商品名称: <input type="text" class="name_1"> <input type="button" class="btn_1" value="查找">
<table border='1'>
<thead style="background-color: orange; color: black;">
<tr>
<th>商品名称</th>
<th>数量</th>
<th>价格</th>
<th>操作</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<div>添加商品页面</div>
商品名称: <input type="text" class="name" id="inputId_1"> </br>
数量； <input type="text" class="num" id="inputId_2"> </br>
单价； <input type="text" class="price" id="inputId_3"> </br>
<input type="button" class='btn' value="添加"> </br>
</body>
</html>
javascript代码:
$.ajax({
url:'goods',
type:'get',
success:function(value){
var arr = value.data
console.log(arr)
for(var i=0;i<arr.length;i++){
$("tbody").append("<tr>"+
"<td>"+arr[i].name+"</td>"+
"<td>"+arr[i].num+"</td>"+
"<td>"+arr[i].price+"</td>"+
"<td>"+
"<input type='button' value='修改' class='change' index_1='"+arr[i].id+"'>"+
"<input type='button' class='delete' value='删除' index='"+arr[i].id+"'>"+
"</td>"+
"</tr>")
}
},
error:function(){
alert("进不去")
}
})
$('tbody').on("click",".delete",function(){
var id=$(this).attr("index")
$.ajax({
url:'delete_goods',
type:'post',
data:{
id
},
success:function(value){
alert(value)
location.reload()
},
error:function(){
alert("删除未进入")
}
})
})
$(".btn").on('click',function(){
var name=$(".name").val()
var num=$(".num").val()
var price=$(".price").val()
$.ajax({
url:"add_goods",
type:"post",
data:{
name,
num,
price,
},
success:function(value){
alert(value)
location.reload()
},
error:function(){
alert("添加进不去")
}
})
})
$(".btn_1").on('click',function(){
var name=$(".name_1").val()
$.ajax({
url:"find_goods",
type:"get",
data:{
name
},
success:function(value){
var arr = value.data
for(var i=0;i<arr.length;i++){
alert(arr[i].name+" "+arr[i].num+" "+arr[i].price)
}
},
error:function(){
alert("寻找进不去")
},
})
})
var butt=true
var id_1
$('tbody').on("click",".change",function(){
id_1=$(this).attr("index_1")
var id=$(this).attr("index_1")
var inputId_1 = document.getElementById("inputId_1");
var inputId_2 = document.getElementById("inputId_2");
var inputId_3 = document.getElementById("inputId_3");
if(butt){
butt=false
$.ajax({
url:"find_goods1",
type:"get",
async:false,
data:{
id
},
success:function(value){
var arr = value.data
for(var i=0;i<arr.length;i++){
inputId_1.value=arr[i].name
inputId_2.value=arr[i].num
inputId_3.value=arr[i].price
}
},
error:function(){
alert("寻找进不去")
}
})
}else{
var id=id_1
var name=$(".name").val()
var num=$(".num").val()
var price=$(".price").val()
butt=true
$.ajax({
url:"update_goods",
type:"post",
data:{
id,
name,
num,
price
},
success:function(value){
alert(value)
location.reload()
},
error:function(){
alert("寻找进不去")
},
})
}
})
增添功能:
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class add_goods
*/
@WebServlet("/add_goods")
public class add_goods extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public add_goods() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String name=request.getParameter("name");
String num=request.getParameter("num");
String price=request.getParameter("price");
String sql="insert into tb_2(name,num,price) value(\""+name+"\","+num+",\""+price+"\")";
int num_1=MysqlUtil.add(sql);
String res="";
if(num_1>0) {
res="添加成功";
}else {
res="添加失败";
}
response.getWriter().write(res);
}
}
删减功能:
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class delete_goods
*/
@WebServlet("/delete_goods")
public class delete_goods extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public delete_goods() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String id=request.getParameter("id");
String sql="delete from tb_2 where id= "+id;
int num=MysqlUtil.del(sql);
String res="";
if(num>0) {
res="删除成功";
}else {
res="删除失败";
}
response.getWriter().write(res);
}
}
查找功能:
import java.io.IOException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class find_goods
*/
@WebServlet("/find_goods")
public class find_goods extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public find_goods() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/json;charset=utf-8");
String name = request.getParameter("name");
String sql = "select * from tb_2 where name=\"" + name + "\"";
String []colums= {"id","name","num","price"};
String res = MysqlUtil.getJsonBySql(sql, colums);
response.getWriter().write(res);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
}
改数据功能:
代码一:
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class update_goods
*/
@WebServlet("/update_goods")
public class update_goods extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public update_goods() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String id=request.getParameter("id");
String name=request.getParameter("name");
String num=request.getParameter("num");
String price=request.getParameter("price");
System.out.println(id);
System.out.println(name);
String sql="UPDATE tb_2 SET name = \""+ name +" \", num = \" "+ num +"\", price =\""+ price +"\" WHERE id = \""+ id +"\"";
int num_1=MysqlUtil.update(sql);
String res="";
if(num_1>0) {
res="更新成功";
}else {
res="更新失败";
}
response.getWriter().write(res);
}
}
代码二:
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class find_goods1
*/
@WebServlet("/find_goods1")
public class find_goods1 extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public find_goods1() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/json;charset=utf-8");
String id = request.getParameter("id");
String sql = "select * from tb_2 where id=\"" + id + "\"";
String []colums= {"id","name","num","price"};
String res = MysqlUtil.getJsonBySql(sql, colums);
response.getWriter().write(res);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
增删改查功能的总体实现代码:
代码一：
import java.rmi.StubNotFoundException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MysqlUtil {
public static void main(String[] args) {
// String sqlcount = "select count(*) from admin";
// String sql = "select * from admin";
// String[] colums = {"id","account","password"};
// String json = getJsonBySql(sqlcount, sql, colums);
// System.out.println(json);
// String sql = "insert into tb_1(name,age,sex) values(\"张三\",21,\"男\")";
// System.out.println(add(sql));
// String sqldel = "delete from tb_1 where id=6";
// System.out.println(del(sqldel));
/*String sqlupdate ="update gq_user set age = 30 where username=\"gs1\"";
System.out.println(update(sqlupdate));*/
/*String sql = "select count(*) from gq_user";
System.out.println(getCount(sql));*/
// String[] col = {"id","name","age"};
// String sql = "select * from tb_1";
//
// String strJson = getJsonBySql(sql,col);
// System.out.println(strJson);
/*String sqlcount = "select count(*) from gq_user";
String sql="select * from gq_user";
String[] col = {"username","age","id","sex","salary"};
System.out.println(getJsonBySql(sqlcount, sql, col));*/
/*String sql = "select * from t_user";
String[] column = {"id","username","password","available"};
ArrayList<String[]> strings = MysqlUtil.showUtil(sql, column);
for (String[] string : strings) {
System.out.println(Arrays.toString(string));
}*/
}
/*添加*/
public static int add(String sql) {
// System.out.println("sql语句是：" + sql);
int i=0;
//数据库连接
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
i=preStmt.executeUpdate();
preStmt.close();
db.close();//关闭连接
System.out.println("数据插入成功，sql语句是：" + sql);
} catch (Exception e) {
e.printStackTrace();
}
return i;//返回影响的行数，1为执行成功;
}
/*修改数据*/
public static int update(String sql) {
int i =0;
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
i = preStmt.executeUpdate();
preStmt.close();
db.close();
System.out.println("数据更新成功，sql语句是：" + sql);
} catch (SQLException e) {
e.printStackTrace();
}
return i;
}
/*删除*/
public static int del(String delstr) {
int i=0;
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(delstr);
i=preStmt.executeUpdate();
//executeUpdate()返回受影响的行数
preStmt.close();
db.close();
System.out.println("数据删除成功，sql语句是：" + delstr);
} catch (SQLException e){
e.printStackTrace();
}
return i;
}
/*查询数量*/
public static int getCount(String sql) {
int sum = 0;
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while (rs.next()) {
sum += rs.getInt(1);
}
rs.close();
db.close();
} catch (Exception e) {
// TODO: handle exception
}
return sum;
}
/**
*功能描述 查询json数据带数据总量
* @author 郭帅
* @date 2021-03-22 10:30
* @param sqlcount 查询数量的sql
* @param sql 查询具体数据的sql
* @param colums 查询的字段
* @return java.lang.String
*/
public static String getJsonBySqlDataGrid( String sqlcount,String sql,String[] colums){
int count = getCount(sqlcount);
System.err.println("标红信息展示sql:" + sql);
ArrayList<String[]> result = new ArrayList<String[]>();
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
String[] dataRow = new String[colums.length];
for( int i = 0; i < dataRow.length; i++ ) {
dataRow[i] = rs.getString( colums[i] );
}
result.add(dataRow);
}
rs.close();
db.close();//
} catch (SQLException e) {
e.printStackTrace();
}
return listToJsonDataGrid(result,colums,count);
}
/**
*功能描述 查询json数据
* @author 郭帅
* @date 2021-03-22 10:30
* @param sql 查询具体数据的sql
* @param colums 查询的字段
* @return java.lang.String
*/
public static String getJsonBySql( String sql,String[] colums){
System.err.println("标红信息展示sql:" + sql);
ArrayList<String[]> result = new ArrayList<String[]>();
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
String[] dataRow = new String[colums.length];
for( int i = 0; i < dataRow.length; i++ ) {
dataRow[i] = rs.getString( colums[i] );
}
result.add(dataRow);
}
rs.close();
db.close();//
} catch (SQLException e) {
e.printStackTrace();
}
return listToJson(result,colums);
}
/**
*功能描述 查询数据
* @author 郭帅
* @date 2021-03-22 10:38
* @param sql 查询具体数据的sql
* @param colums 查询的字段
* @return java.util.ArrayList<java.lang.String[]>
*/
public static ArrayList<String[]> showUtil( String sql, String[] colums){
ArrayList<String[]> result = new ArrayList<String[]>();
DBConnection db = new DBConnection();
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
String[] dataRow = new String[colums.length];
for( int i = 0; i < dataRow.length; i++ ) {
dataRow[i] = rs.getString( colums[i] );
}
result.add(dataRow);
}
rs.close();
db.close();//
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 转化为json数据字符串
* @param
* @return
*/
public static String listToJsonDataGrid( ArrayList<String[]> list,String[] colums,int count) {
String jsonStr = "{\"code\":0,\"msg\":\"成功了\",\"count\":"+count+",\"data\":[";
for(int i = 0; i < list.size(); i++) {
String arr = "{";
for( int j = 0; j < list.get(0).length; j++) {
if( list.get(i)[j] == null || "NULL".equals(list.get(i)[j])) {
arr += "\"" + colums[j] + "\":\"\"";
}else {
arr += "\"" + colums[j] + "\""+":" ;
arr += "\"" + list.get(i)[j].replace("\"","\\\"") + "\"";
}
if( j < list.get(0).length - 1 ) {
arr += ",";
}
}
arr += "}";
if( i < list.size() - 1 ) {
arr += ",";
}
jsonStr += arr;
}
jsonStr += "]}";
return jsonStr;
}
/**
* 转化为json数据字符串
* @param
* @return
*/
public static String listToJson( ArrayList<String[]> list,String[] colums) {
String jsonStr = "{\"code\":0,\"msg\":\"成功了\",\"data\":[";
for(int i = 0; i < list.size(); i++) {
String arr = "{";
for( int j = 0; j < list.get(0).length; j++) {
if( list.get(i)[j] == null || "NULL".equals(list.get(i)[j])) {
arr += "\"" + colums[j] + "\":\"\"";
}else {
arr += "\"" + colums[j] + "\""+":" ;
arr += "\"" + list.get(i)[j].replace("\"","\\\"") + "\"";
}
if( j < list.get(0).length - 1 ) {
arr += ",";
}
}
arr += "}";
if( i < list.size() - 1 ) {
arr += ",";
}
jsonStr += arr;
}
jsonStr += "]}";
return jsonStr;
}
}
代码二:
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public static void main(String[] args) {
}
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:1111/hbu?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false";
String user = "root";
String password = "123456";
public Connection conn;
public DBConnection() {
try {
Class.forName(driver);
conn = (Connection) DriverManager.getConnection(url, user, password);//
// if(!conn.isClosed())
// System.out.println("Succeeded connecting to the Database!");
} catch (Exception e) {
e.printStackTrace();
}
}
public void close() {
try {
this.conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}