DAO模式数据库操作源代码

package cn.mldn.lxh.Dao.Impl; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import cn.mldn.lxh.Dao.userDAO; import cn.mldn.lxh.Dao.dbcon.User; import cn.mldn.lxh.Dao.dbcon.dbConnection; public class userDAOImpl implements userDAO{ public void insert(User user) throws Exception { System.out.println("-----insert----"); dbConnection conn=new dbConnection(); PreparedStatement pstmt=null; String sql="insert into user_information(name,password,age,email) values(?,?,?,?)"; System.out.println(sql); System.out.println(user.getUserName()); try{ System.out.println("start insert"); pstmt=conn.getConnection().prepareStatement(sql); System.out.println(sql); pstmt.setString(1, user.getUserName()); System.out.println("insert beginning"); pstmt.setString(2, user.getPassword()); pstmt.setInt(3, user.getAge()); pstmt.setString(4, user.getEmail()); pstmt.executeUpdate(); System.out.println("insert implement...."); pstmt.close(); } catch(Exception e) { System.out.println("insert fail"); e.printStackTrace(); throw new Exception("操作中出现错误"); } finally{ conn.close(); } } public void update(User user) throws Exception { dbConnection conn=null; PreparedStatement pstmt=null; String sql="update user_information set name=?,password=?,age=?,email=? where id=?"; try{ pstmt=conn.getConnection().prepareStatement(sql); pstmt.setString(1, user.getUserName()); pstmt.setString(2, user.getPassword()); pstmt.setInt(3, user.getAge()); pstmt.setString(4, user.getEmail()); pstmt.setInt(5, user.getId()); pstmt.executeUpdate(); pstmt.close(); } catch(Exception e) { throw new Exception("操作中出现错误"); } finally{ conn.close(); } } public void delete(int id) throws Exception { dbConnection conn=null; PreparedStatement pstmt=null; String sql="delete from user_information where id=?"; try{ pstmt=conn.getConnection().prepareStatement(sql); pstmt.setInt(1, id); pstmt.executeUpdate(); pstmt.close(); } catch(Exception e) { throw new Exception("操作中出现错误"); } finally{ conn.close(); } } public User queryById(int id) throws Exception { dbConnection conn=null; User user=null; PreparedStatement pstmt=null; String sql="select * from user_information where id=?"; try{ pstmt=conn.getConnection().prepareStatement(sql); pstmt.setInt(1, id); ResultSet rs=pstmt.executeQuery(); if(rs.next()) { user=new User(); user.setUserName(rs.getString("name")); user.setPassword(rs.getString("password")); user.setAge(rs.getInt("age")); user.setEmail(rs.getString("email")); } pstmt.close(); } catch(Exception e) { throw new Exception("操作中出现错误"); } finally{ conn.close(); } return user; } public List queryAll() throws Exception { List all=null; dbConnection conn=null; String sql="select * from user_information"; PreparedStatement pstmt=null; try{ conn=new dbConnection(); pstmt=conn.getConnection().prepareStatement(sql); ResultSet rs=pstmt.executeQuery(); while(rs.next()) { User user=new User(); user.setId(rs.getInt("id")); user.setUserName(rs.getString("name")); user.setPassword(rs.getString("password")); user.setAge(rs.getInt("age")); user.setEmail(rs.getString("email")); all.add(user); } }catch(Exception e){} finally{conn.close();} return all; } public List queryByLike(String cond) throws Exception { List all=new ArrayList(); dbConnection conn=null; PreparedStatement pstmt=null; String sql="select * from user_information where name like ? or remark like ?"; try{ conn=new dbConnection(); pstmt=conn.getConnection().prepareStatement(sql); pstmt.setString(1, cond); pstmt.setString(2, cond); ResultSet rs=pstmt.executeQuery(); while(rs.next()) { User user=new User(); user.setId(rs.getInt("id")); user.setUserName(rs.getString("username")); user.setPassword(rs.getString("password")); user.setAge(rs.getInt("age")); user.setEmail(rs.getString("email")); all.add(user); } }catch(Exception e){} finally{conn.close();} return all; } }