基于servlet和mysql的简易新闻系统

项目结构在这里插入图片描述

前端页面:
登录页面:login.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="css/style.css">
    <title>登录页面</title>
    <script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js"></script>
</head>
<body>
    <div class="whole">
        <p class="top">    
                 	请输入用户名和密码
        </p>
        <div class="input">
            <div>
                	用户名	
            </div>
            <input type="text" id="username">
        </div>
        <div class="input">
            <div>
                	密码
            </div>
            <input type="password" id="password">
        </div>
        <div class="bottom">
            <input type="submit" value="登录" onclick="login()">
        </div>
    </div>
</body>
<script>
	function login(){
		var username=$("#username").val();
		var password=$("#password").val();
		$.ajax({
			url:"/WebNewLearn/LoginServlet",
			data:{
				"username":username,
				"password":password
			},
			async:true,
			type:"post",
			success:function(data){
				if(data.data.length>0){
					location.href="/WebNewLearn/index.html";
				}
				else{
					alert("用户名或密码错误!");
				}
			},
			error(){
				alert("登陆失败!");
			}
			
		})
	}
</script>
</html>

style.css

*{
    margin:0;
    padding:0;
}
body{
    background-image: url("../img/background.jpg");
    background-position: center;
    background-attachment: fixed;
    background-size: cover;
    background-repeat: no-repeat;
}
.whole{
    width: 30%;
    min-width: 300px;
    margin: auto;
    margin-top:150px ;
    /* position: center; */
    /* top: 30%; */
    background-color: white;
    opacity: 0.7;
    padding-bottom: 2px;
}
.top{
    height:40px;
    margin:auto;
    font-size:16px;
    text-align: center;
    line-height:40px;
    background: linear-gradient(to left,red,blue);
}
.input{
    margin: 10px;
}
.input div{
    font-size: 16px;
    color: rgb(112, 205, 241);
    text-align: center;
    line-height:20px;
}
.input input{
    margin-top: 10px;
    width:84%;
    height: 32px;
    /* (100-84)/2 */
    margin-left: 8%;
    border-width:1px;
    border:1px solid black;
    outline:none;
    text-indent:10px;
    color:rgb(17, 16, 16);
}
/* , */
.input input:hover{
    box-shadow: 2px 2px 2px rgb(158, 154, 154);
}

.bottom{
    width: 84%;
    margin:30px auto;
    /* margin-bottom: 40px; */
    height: 40px;
    text-align: center;
    background: linear-gradient(to left,red,blue);
}
.bottom input{
    display:block;
    width:100%;
    height:100%;
}
.bottom a:hover{
    box-shadow:2px 2px wpx black;
}



页面效果
在这里插入图片描述
index.html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>主页面</title>
<script src="https://cdn.staticfile.org/jquery/1.10.2/jquery.min.js"></script>
<script src="layer/layer.js"></script>
<link rel="stylesheet" href="css/index.css">
<link rel="stylesheet" href="css/layui.css">

</head>
<body>
	<button type="button" id="add" class="layui-btn layui-btn-lg layui-btn-radius layui-btn-normal">添加一条新闻</button>
   <div class="top">
        <h2>新闻查询</h2>
       	 按照编号查询:
    	    <input type="text" name="" id="id">
       	按照标题查询:
   	  	   <input type="text" name="" id="title">
      	  按照作者查询:
     	   <input type="text" name="" id="author">
 	       按照分类查询:
	        <select id="group" >
	        	<option value="1">国际新闻</option>
	        	<option value="2">娱乐新闻</option>
	        	<option value="3">军事新闻</option>
	        	<option value="4">头条新闻</option>
	        </select>
			<button type="button" id="search" class="layui-btn">查询</button>
    </div>
    <div class="center">
        <hr>
        <div class="content">
            <h1>新闻展示</h1>
            <table border="1">
                <tr>
                    <th>编号</th>
                    <th>标题</th>
                    <th>分类</th>
                    <th>作者</th>
                    <th>内容摘要</th>
                    <th>操作</th>
                </tr>
                <tbody class="t_content">
                	
                </tbody>
            </table>
        </div>
    </div>
    
</body>
<script type="text/javascript">
	function init(){
		$.ajax({
			url:"mainServlet",
			async:true,
			type:"post",
			data:{
				"action":"findAll"
			},
			success:function(data){
					if(data.code==200){
						data=data.data;
						$(".t_content").empty();
						for(var i = 0;i<data.length;i++){
							$(".t_content").append("<tr><th>"+data[i][0]+"</th><th>"+data[i][1]+"</th><th>"+data[i][2]+"</th><th>"+data[i][3]+"</th><th>"+data[i][4]+"</th><th><button type='button' class='layui-btn del'>删除</button><button type='button' class='layui-btn update'>修改</button></th></tr>");
						}
					}
					//JSON.parse();//把json字符串转成json对象,不转换之前一般就是字符串类型
					//json数据类型string number boolean undefined null object function array 前五种是基本类型,后三种是复合类型
					console.log(typeof data);
				},
			error(){
				alert("请求失败!");
			}
				
		});
	}
	init();
</script>
<script>
	$(document).on("click","#search",function (){
		//两种方法都可以
		var title=$("#title").val();//最后的括号如果不加会出现undefined的值
		var id=document.getElementById("id").value;
		var author=$("#author").val();
		var group=$("#group").val();
		$.ajax({
			url:"mainServlet",
			async:true,
			type:"post",
			data:{
				"action":"find",
				"title":title,
				"id":id,
				"author":author,
				"group":group
			},
			success:function(data){
					if(data.code==200){
						data=data.data;
						if(data.length!=0){
							$(".t_content").empty();
							for(var i = 0;i<data.length;i++){
								$(".t_content").append("<tr><th>"+data[i][0]+"</th><th>"+data[i][1]+"</th><th>"+data[i][2]+"</th><th>"+data[i][3]+"</th><th>"+data[i][4]+"</th><th><button type='button' class='layui-btn del'>删除</button><button type='button' class='layui-btn update'>修改</button></th></tr>");
							}
						}
						else{
							$(".t_content").html("<tr><th colspan='6'>暂无信息!</th></tr>");
						}
					}
					//JSON.parse();//把json字符串转成json对象,不转换之前一般就是字符串类型
					//json数据类型string number boolean undefined null object function array 前五种是基本类型,后三种是复合类型
					console.log(typeof data);
				},
			error(){
				alert("请求失败!");
			}
			
		});
	})
	//添加部分
	var add_index=0;
	$(document).on("click","#add",function (){
		add_index=layer.open({
			  type: 1,
			  skin: 'layui-layer-rim', //加上边框
			  area: ['820px', '840px'], //宽高
			  content: "<div class=\"add\">\r\n" + 
				"    	<div class=\"add_content\">\r\n" + 
				"    	    请输入标题:<input type=\"text\" name=\"\" class=\"add_title\"><br>\r\n" + 
				"		      请输入作者名字:<input type=\"text\" name=\"\" class=\"add_author\"><br>\r\n" + 
				"		       请输入分类:\r\n" + 
				"		        <select name=\"\" class=\"add_group\">\r\n" + 
				"		            <option value=\"1\">国际新闻</option>\r\n" + 
				"		            <option value=\"2\">娱乐新闻</option>\r\n" + 
				"		            <option value=\"3\">军事新闻</option>\r\n" + 
				"		            <option value=\"4\">头条新闻</option>\r\n" + 
				"		        </select><br>\r\n" + 
				"		        请输入主体内容:<textarea name=\"\" class=\"add_con\" cols=\"30\" rows=\"10\"></textarea>\r\n" + 
				"    		<button type=\"button\" id=\"add_button\" class=\"layui-btn\">确认添加</button>\r\n" + 
				"    	</div>\r\n" + 
				"	</div>"
		});
	})
	$(document).on("click","#add_button",function(){
		var title=$(".add_title").val();//最后的括号如果不加会出现undefined的值
		var author=$(".add_author").val();
		var group=$(".add_group").val();
		var content=$(".add_con").val();
		if(title==""||author==""||content==""){
			layer.msg("请确认信息输入完整!");
		}else{
			$.ajax({
				url:"mainServlet",
				async:true,
				type:"post",
				data:{
					"action":"add",
					"title":title,
					"author":author,
					"group":group,
					"content":content
				},
				success:function(data){
						if(data==1){
							init();
							layer.msg("添加成功!",{icon:1});
							layer.close(add_index);
							
						}else{
							layer.msg("请确认数据是否输入完整!",{icon:2});
						}
						//JSON.parse();//把json字符串转成json对象,不转换之前一般就是字符串类型
						//json数据类型string number boolean undefined null object function array 前五种是基本类型,后三种是复合类型
						console.log(typeof data);
					},
				error(){
					layer.alert("请求失败!",{icon:2});
				}
				
			});
		}
		
	})
	//删除
	$(document).on("click",".del",function (){
		var id=$(this).parent().parent().children().first().text();
		layer.confirm('您确认要删除吗?', {
			  btn: ['确认','取消'] //按钮
			}, function(){
				console.log(id);
				$.ajax({
					url:"mainServlet",
					async:true,
					type:"post",
					data:{
						"action":"delete",
						"id":id
					},
					success:function(data){
							if(data==1){
								init();
								layer.msg("删除成功!",{icon:1});
							}else{
								layer.alert("请确认数据是否输入完整!");
							}
							console.log(typeof data);
						},
					error(){
						layer.alert("请求失败!");
					}
					
				});
			}, function(){
			  layer.msg('撤销成功!', {
			    time: 2000
			  });
			});
		
	})
	//修改
	var update_index = 0;
	$(document).on("click",".update",function(){
		var id=$(this).parent().parent().children().eq(0).text();
		var title=$(this).parent().parent().children().eq(1).text();//最后的括号如果不加会出现undefined的值
		var group=$(this).parent().parent().children().eq(2).text();
		var author=$(this).parent().parent().children().eq(3).text();
		var content=$(this).parent().parent().children().eq(4).text();
		console.log(id);
			update_index=layer.open({
			  type: 1,
			  skin: 'layui-layer-rim', //加上边框
			  area: ['820px', '840px'], //宽高
			  content: "<div class=\"upd\">\r\n" + 
				"    	<div class=\"update_content\">\r\n" + 
				"<input type=\"text\" style=\"display:none\" class=\"update_id\">"+
				"    	    请输入标题:<input type=\"text\" name=\"\" class=\"update_title\"><br>\r\n" + 
				"		      请输入作者名字:<input type=\"text\" name=\"\" class=\"update_author\"><br>\r\n" + 
				"		       请输入分类:\r\n" + 
				"		        <select name=\"\" class=\"update_group\">\r\n" + 
				"		            <option value=\"1\">国际新闻</option>\r\n" + 
				"		            <option value=\"2\">娱乐新闻</option>\r\n" + 
				"		            <option value=\"3\">军事新闻</option>\r\n" + 
				"		            <option value=\"4\">头条新闻</option>\r\n" + 
				"		        </select><br>\r\n" + 
				"		        请输入主体内容:<textarea name=\"\" class=\"update_con\" cols=\"30\" rows=\"10\"></textarea>\r\n" + 
				"    		<button type=\"button\" class=\"layui-btn update_button\">确认修改</button>\r\n" + 
				"    	</div>\r\n" + 
				"	</div>"
			});
			$(".update_id").val(id);
			$(".update_title").val(title);
			$(".update_author").val(author);
			//给select赋值不是给select的value赋值
			$(".update_group").attr("text", group); 
			$(".update_con").val(content);
	})
	$(document).on("click",".update_button",function(){
		id=$(".update_id").val();
		title=$(".update_title").val();//最后的括号如果不加会出现undefined的值
		author=$(".update_author").val();
		group=$(".update_group").val();
		content=$(".update_con").val();
		if(title==""||author==""||content==""){
			layer.msg("请确认信息输入完整!");
		}else{
			$.ajax({
				url:"mainServlet",
				async:true,
				type:"post",
				data:{
					"action":"update",
					"id":id,
					"title":title,
					"author":author,
					"group":group,
					"content":content
				},
				success:function(data){
						if(data==1){
							init();
							layer.msg("修改成功!",{icon:1});
							layer.close(update_index);
						}else{
							layer.alert("请确认数据是否输入完整!",{icon:2});
						}
						
						document.getElementById("confirm").style.display="none";
						document.getElementById("add").style.display="inline";
						document.getElementById("search").style.display="inline";
					},
				error(){
					layer.alert("请求失败!",{icon:2});
				}
				
			});
		}
		
	})
</script>
</html>

index.css:

select{
	text-align: center;
	text-align-last: center;
}
.top{
    width: 50%;
    margin: 0 auto;
    text-align: center;
    font-size: 18px;
}
.top input{
    display: block;
    margin: 10px auto;
    width: 50%;
    height: 20px;
    text-indent: 10px;
}
#group{
	    display: block;
	    margin: 10px auto;
	    width: 30%;
	    height: 40px;
	    font-size:22px;
	    text-align:center
}
.top button{
    display: block;
    margin: 10px auto;
    width: 30%;
    height: 45px;
    text-indent: 10px;
    font-size: 20px;
}
.center{
    width: 100%;
}
.content{
    width: 60%;
    margin: 0 auto;
    text-align: center;
}
.content table{
    margin: 0 auto;
    width: 100%;
    border-collapse: collapse;
    border:1px solid black
}
.content table th{
    height: 50px;
}
.content table th button{
	display:inline;
	margin:0 10px;
    height: 40px;
    width:60px;
}
body .add .add_content{
	color:black;
    text-align: center;
    width:100%
}
.add_content input{
    display: block;
    margin: 10px auto;
    width: 40%;
    height: 20px;
    text-indent: 10px;
}
.add_content select{
    display: block;
    margin: 10px auto;
    width: 20%;
    height: 30px;
    text-indent: 10px;
}
.add_content textarea{
    display: block;
    margin: 10px auto;
    width: 60%;
    height: 300px;
    text-indent: 10px;
    font-size: 20px;
    outline: none;

}
#add{
    position: fixed;
    top:40%;
    left:10%
}
#add_button{
	width:20%;
	margin:20px;
}
body .upd .update_content{
	color:black;
    text-align: center;
    width:100%
}
.update_content input{
    display: block;
    margin: 10px auto;
    width: 40%;
    height: 20px;
    text-indent: 10px;
}
.update_content select{
    display: block;
    margin: 10px auto;
    width: 20%;
    height: 30px;
    text-indent: 10px;
}
.update_content textarea{
    display: block;
    margin: 10px auto;
    width: 60%;
    height: 300px;
    text-indent: 10px;
    font-size: 20px;
    outline: none;

}
#update_button{
	width:20%;
	margin:20px;
}

效果
在这里插入图片描述
servlet部分:
loginServlet:

package com.MyNewLearn.web;

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;

import com.MyNewLearn.db.MysqlUtil;

/**
 * Servlet implementation class LoginServlet
 */
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LoginServlet() {
        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());
		doPost(request, response);
	}

	/**
	 * @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");
		response.setContentType("text/json;charset=utf-8");
		//如果是json是json格式,在前台不用把字符串转化成对象,如果是html,需要转化
		String username =request.getParameter("username");
		String password =request.getParameter("password");
		String sql="select * from user where ";
		String [] colums= {"username","password"};
		String jsondata ="";
		System.out.println(username);
		System.out.println(password);
		if (!username.equals("")) {
			sql+="username = \""+username+"\" and password = \""+password+"\"";
			MysqlUtil mysqlUtil=new MysqlUtil();
			jsondata =MysqlUtil.getJsonBySql(sql, colums);
		}
		
		
		response.getWriter().append(jsondata);
	}

}

mainServlet:

package com.MyNewLearn.web;

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;

import com.MyNewLearn.db.MysqlUtil;

/**
 * Servlet implementation class mainServlet
 */
@WebServlet("/mainServlet")
public class mainServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public mainServlet() {
        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");
		response.setContentType("text/json;charset=utf-8");
		//如果是json是json格式,在前台不用把字符串转化成对象,如果是html,需要转化
		String id =request.getParameter("id");
		String title =request.getParameter("title");
		String author =request.getParameter("author");
		String group =request.getParameter("group");
		String content=request.getParameter("content");
		String action=request.getParameter("action");
		String sql="";
		String sql1="";
		String [] colums= {"id","title","group_name","author","content"};
		MysqlUtil mysqlUtil=new MysqlUtil();
		String jsondata="";
		int result = 0;
		int result1 = 0;
		//查询部分
		if (action.equals("findAll")) {
			sql+="select * from news n,news_con nc where n.group_id=nc.id group by n.id";
		}
		if (action.equals("find")) {
			sql+="select * from news n,news_con nc where n.group_id=nc.id ";
			if (!title.equals("")) {
				sql+=" and n.name like \"%"+title+"%\"";
			}
			if (!id.equals("")) {
				sql+=" and n.id = \""+id+"\"";
			}
			if (!author.equals("")) {
				sql+=" and n.age = \""+author+"\"";
			}
			if (!group.equals("")) {
				sql+=" and n.group_id = \""+group+"\"";
			}
			sql+="group by n.id";
		}
		//添加部分
		if (action.equals("add")) {
			sql+="insert into news (title,author,group_id,content) values(\""+title+"\",\""+author+"\",\""+group+"\",\""+content+"\")";
		}
		//删除部分
		if(action.equals("delete")&&!id.equals("")) {
			if(!id.equals("")) {
				sql+="delete from news where id = \""+id+"\"";
			}
			
		}
		//更新部分
		if(action.equals("update")) {
				sql+="UPDATE news n SET n.title = \""+title+"\" ";
				sql+=", n.author = \""+author+"\" ";
				sql+=", n.group_id = \""+group+"\" ";
				sql+=", n.content = \""+content+"\" ";
				sql+=" WHERE n.id = \""+id+"\""; 
		}

		if (action.equals("findAll")||action.equals("find")) {
			jsondata =MysqlUtil.getJsonBySql(sql, colums);
			System.out.println(jsondata);
			response.getWriter().append(jsondata);
		}
		else {
			result = MysqlUtil.update(sql);
			response.getWriter().print(result);
			
		}
		
	}

}

后台连接数据库部分:
DBConnection.java

package com.MyNewLearn.db;

import java.sql.Connection;
import java.sql.DriverManager;

public class DBConnection {

	public static void main(String[] args) {

	}

	String driver = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://49.232.18.90:3306/myTest?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false";
	String user = "root";
	String password = "wxw123456789";

	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();
		}
	}

}

MysqlUtil部分

package com.MyNewLearn.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MysqlUtil {

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//String sql = "insert into userinfo (id,`name`,age,otherinfo) values (1500,'吴强3',20,'很英俊,十分帅气')";
		//add(sql);
		
		//String sqldel = "delete from userinfo where id = 1500";
		//del(sqldel);
		
		String[] col = {"id","name","age"};
		String sql = "select * from test order by id asc limit 10";
		String strJson = MysqlUtil.getJsonBySql(sql, col);
		System.out.println(strJson);
//		ArrayList< String[] > data = MysqlUtil.showUtil(sql, col);
//		System.out.println(  data.get(3)[2] );
//		
//		String str = MysqlUtil.show(sql, col);
//		
//		System.out.println(  str );
	}
	
	/**
	 * 用于插入数据
	 * @param sql insert语句
	 * @return
	 */
	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);
            preStmt.executeUpdate();
            preStmt.close();
            db.close();//关闭连接 
            i = 1;
           // System.out.println("数据插入成功,sql语句是:" + sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;//返回影响的行数,1为执行成功;
    }
    //
    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();
        } 
         
         return result;
    }
    
    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);
   }

    public static int update(String sql) {
        int i =0;
        DBConnection db = new DBConnection();
        try {
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
            preStmt.executeUpdate();
            preStmt.close();
            db.close();
            i = 1;
            System.out.println("数据更新成功,sql语句是:" + sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    

    /**
     *  查询数据
     * @param sql select * from 表
     * @param params [id,name,sex,age] 我们要查询的列名的一个字符串数组
     * @return
     */
    public static String show(String sql, String[] params){
    	
    	List< Map<String,String> > listmap = new ArrayList<>();
    	
         DBConnection db = new DBConnection();
         ResultSet rs = null;
         try {
            Statement stmt = (Statement) db.conn.createStatement();
            rs = (ResultSet) stmt.executeQuery(sql);
            while(rs.next()){
            	Map<String,String> map = new HashMap<String,String>();
            	for(int i = 0; i < params.length; i++) {
            		map.put(params[i], rs.getString(params[i]));
            	}
            	listmap.add(map);
            }
            rs.close();
            db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
		return mapToJson(listmap); 
         
     
    }

    
  
    public static int del(String delstr) {
        int i=0;
        DBConnection db = new DBConnection();
        try {    
            PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(delstr);
            preStmt.executeUpdate();
            
            preStmt.close();
            db.close();
            i = 1;
            System.out.println("数据删除成功,sql语句是:" + delstr);
        } catch (SQLException e){
            e.printStackTrace();
        }
        return i;
    }

    
    /**
     * map转化为json数据字符串
     * @param maplist
     * @return
     */
    public static String mapToJson( List<Map<String,String>> maplist ) {
    	String jsonData = "{ \"data\":[";
		for(int i = 0; i < maplist.size(); i++) {
			String outstr = "[\"" ;
			int size = 0;
			for(String value : maplist.get(i).values()){
				size += 1;
				outstr += value;
				if( size < maplist.get(i).values().size() ) {
				     outstr += "\",\"";
				}
			}
		    outstr += "\"]";
		    
		    if(i < maplist.size() -1) {
		    	outstr += ",";
		    }
			jsonData += outstr;
			
		}
		jsonData += "]}";
		
		return jsonData;
    }
    
    public static String listToJson( ArrayList<String[]> list) {
    	
    	String jsonStr = "{\"code\":200,\"message\":\"成功了\",\"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 += "\"\"";
    					}else {
    						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;
    }

}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值