MySQL数据库设计
数据库名user1
项目结构:
util层
DBUtil.java
package util;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtil {
private static String Driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8";
private static String user = "root";
private static String psd = "root";
private static Connection conn = null;
// 连接数据库
public static Connection getConnection() {
if (conn == null) {
try {
Class.forName(Driver);
conn = DriverManager.getConnection(url, user, psd);
System.out.println("开始数据库连接");
} catch (Exception e) {
e.printStackTrace();
}
}
return conn;
}
// 关闭数据库连接
public static void CloseConnection() {
if (conn != null) {
try {
conn.close();
System.out.println("关闭数据库连接");
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
JSONUtil.java
package util;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONObject;
public class JSONUtil {
// 从输入流中获取JSON数据
public static JSONObject getJSON (HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 输入流
StringBuffer sb = new StringBuffer();
String line = null;
BufferedReader br = request.getReader();
while ((line = br.readLine()) != null) {
sb.append(line);
}
// 返回JSONObject
String jsonStr = sb.toString();
JSONObject jsonObj = JSONObject.fromObject(jsonStr);
return jsonObj;
}
// 返回JSON格式的数据
public static void returnJSON (HttpServletRequest request, HttpServletResponse response, JSONObject jsonObj)
throws ServletException, IOException {
// 设置编码格式,解决乱码
response.setCharacterEncoding("utf-8");
PrintWriter out = response.getWriter() ;
out.write(jsonObj.toString());
out.flush();
out.close();
}
}
dao层
UserDao.java
package dao;
import java.util.List;
import domain.User;
public interface UserDao {
/** 显示用户账号密码*/
public List<User> list();
/** 显示所有用户 */
public List<User> listAll();
/** 添加一个用户 */
public boolean add(User user);
/** 根据id删除一个用户 */
public boolean deleteById(Integer id);
/** 更新一个用户 */
public boolean update(User user);
/** 根据id查找一个用户 */
public User getById(Integer id);
}
UserDaoImpl.java
package dao.Impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import dao.UserDao;
import domain.User;
import util.DBUtil;
public class UserDaoImpl implements UserDao {
@Override
public List<User> list() {
List<User> users = new ArrayList<User>();
Connection conn = DBUtil.getConnection();
String sql = "select account,password from user1";
try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
while (rst.next()) {
User user = new User();
//user.setId(rst.getInt("id"));
user.setAccount(rst.getString("account"));
user.setPassword(rst.getString("password"));
// user.setName(rst.getString("name"));
//user.setInfo(rst.getString("info"));
//user.setCreateTime(rst.getTimestamp("gmt_create"));
// user.setModifiedTime(rst.getTimestamp("gmt_modified"));
users.add(user);
}
rst.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
@Override
public List<User> listAll() {
List<User> users = new ArrayList<User>();
Connection conn = DBUtil.getConnection();
String sql = "select * from user1";
try {
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
while (rst.next()) {
User user = new User();
user.setId(rst.getInt("id"));
user.setAccount(rst.getString("account"));
user.setPassword(rst.getString("password"));
user.setName(rst.getString("name"));
user.setInfo(rst.getString("info"));
//user.setCreateTime(rst.getTimestamp("gmt_create"));
// user.setModifiedTime(rst.getTimestamp("gmt_modified"));
users.add(user);
}
rst.close();
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
@Override
public boolean add(User user) {
Connection conn = DBUtil.getConnection();
String sql = "insert into user1(account, password, name, info) value (?, ?, ?, ?)";
try {
PreparedStatement pst = conn.prepareStatement(sql);
pst.setString(1, user.getAccount());
pst.setString(2, user.getPassword());
pst.setString(3, user.getName());
pst.setString(4, user