JDBC

public class DBUtil {
 private static Connection conn;
 
 public static Connection getConnection(){
  try {
   Class.forName("com.mysql.jdbc.Driver");
   String url = "jdbc:mysql://localhost:3306/fidelity";
   String name = "root";
   String pwd = "root";
   conn = DriverManager.getConnection(url, name, pwd);
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return conn;
 }

 

 

package com.fidelity.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.fidelity.util.DBUtil;
import com.fidelity.vo.User;

//实现对表t_user的增删改查的所有代码
public class UserDAO {

 public boolean save() {
  return true;
 }

 public User findById() {
  return null;
 }

 public ArrayList<User> findAll() {

  ArrayList<User> users = new ArrayList<User>();
  try {
   // 连接数据库的事交给DBUtil
   Connection conn = DBUtil.getConnection();

   Statement stmt = conn.createStatement();
   String sql = "SELECT * FROM t_user";
   ResultSet rs = stmt.executeQuery(sql);
   // 把查询到的数据一个一个放入到ArrayList里，准备回传
   while (rs.next()) {
    int id = rs.getInt(1);
    String uname = rs.getString("uname");
    String upwd = rs.getString(3);

    User u = new User();
    u.setId(id);
    u.setUname(uname);
    u.setUpwd(upwd);

    users.add(u);

    System.out.println(id + "-" + uname + "-" + upwd);
   }
   rs.close();
   stmt.close();

   // 关闭数据库的连接
   DBUtil.close();

  } catch (SQLException e) {
   e.printStackTrace();
  }

  return users;
 }

// public boolean isExists(User user) {
//  boolean result = false;
//  // 连接数据库，执行sql，获得结果，返回
//  try {
//   Connection conn = DBUtil.getConnection();
//   Statement stmt = conn.createStatement();
//   String sql = "SELECT * FROM t_user WHERE uname='"+user.getUname()+"' AND UPWD='"+user.getUpwd()+"'";
//   
//   System.out.println(sql);
//   
//   
//   ResultSet rs = stmt.executeQuery(sql);
//   // 判断是否存在
//   if(rs.next()){
//    result = true;
//   }
//   // close
//   DBUtil.close();
//   
//  } catch (Exception e) {
//   e.printStackTrace();
//  }
//  return result;
// }
 public boolean isExists(User user) {
  boolean result = false;
  // 连接数据库，执行sql，获得结果，返回
  try {
   Connection conn = DBUtil.getConnection();
   // 语句对象
   // Statement stmt = conn.createStatement();
   String sql = "SELECT * FROM t_user WHERE uname=? AND UPWD=?";
   // 预编译语句对象
   PreparedStatement pstmt = conn.prepareStatement(sql);
   // 赋值
   pstmt.setString(1, user.getUname());
   pstmt.setString(2, user.getUpwd());
   
   System.out.println(sql);
   
   
   ResultSet rs = pstmt.executeQuery();
   // 判断是否存在
   if(rs.next()){
    result = true;
   }
   // close
   DBUtil.close();
   
  } catch (Exception e) {
   e.printStackTrace();
  }
  return result;
 }

 public boolean insertToDb(String uname, String upwd) {
  boolean result = false;
  try{
   Connection conn= DBUtil.getConnection();
   //String sql="INSERT INTO t_user(uname,upwd) VALUES('aa','bb')";
   String sql = "INSERT INTO t_user(uname,upwd) VALUES (?,?)";
   PreparedStatement pstmt = conn.prepareStatement(sql);
   pstmt.setString(1, uname);
   pstmt.setString(2, upwd);
   int i=pstmt.executeUpdate();
   if(i==1){
    result=true;
    
   }
   
  }catch(SQLException e){
   
   e.printStackTrace();
  }
  return result;
 }
}