package cn.zcw.util;
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 cn.zcw.model.User;
public class PrepStmtUtil
{
//插入数据
public boolean insert(User user)
{
boolean flag = true;
Connection conn = null;
PreparedStatement ps = null; // 创建PreparedStatement 对象
String sql = "insert into user (name,pwd) values(?,?)"; // sql语句不再采用拼接方式,应用占位符问号的方式写sql语句。
conn = DBUtil.getConn();
try
{
ps = conn.prepareStatement(sql);
ps.setString(1, user.getUsername()); // 对占位符设置值,占位符顺序从1开始,第一个参数是占位符的位置,第二个参数是占位符的值。
ps.setString(2, user.getPassword());
int i = ps.executeUpdate();
if (i == 0)
{
flag = false;
}
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} finally
{
DBUtil.closeAll(null, ps, conn);
}
return flag;
}
//修改数据
public boolean update(User user){
boolean flag=true;
Connection conn=null;
PreparedStatement ps=null;
String sql="update user set pwd=? where name=?";
conn=DBUtil.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, user.getPassword());
ps.setString(2, user.getUsername());
int i= ps.executeUpdate();
if(i==0){
flag=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeAll(null, ps, conn);
}
return flag;
}
//删除数据
public boolean delete(int id){
boolean flag=true;
Connection conn=null;
PreparedStatement ps=null;
String sql="delete from user where id=?";
conn=DBUtil.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, id);
int i=ps.executeUpdate();
if(i==0){
flag=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeAll(null, ps, conn);
}
return flag;
}
//查询数据
public List<User> query(){
List<User> list= new ArrayList<User>();
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String sql="select * from user";
conn=DBUtil.getConn();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
User user= new User();
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("pwd"));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, ps, conn);
}
return list;
}
}
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 cn.zcw.model.User;
public class PrepStmtUtil
{
//插入数据
public boolean insert(User user)
{
boolean flag = true;
Connection conn = null;
PreparedStatement ps = null; // 创建PreparedStatement 对象
String sql = "insert into user (name,pwd) values(?,?)"; // sql语句不再采用拼接方式,应用占位符问号的方式写sql语句。
conn = DBUtil.getConn();
try
{
ps = conn.prepareStatement(sql);
ps.setString(1, user.getUsername()); // 对占位符设置值,占位符顺序从1开始,第一个参数是占位符的位置,第二个参数是占位符的值。
ps.setString(2, user.getPassword());
int i = ps.executeUpdate();
if (i == 0)
{
flag = false;
}
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} finally
{
DBUtil.closeAll(null, ps, conn);
}
return flag;
}
//修改数据
public boolean update(User user){
boolean flag=true;
Connection conn=null;
PreparedStatement ps=null;
String sql="update user set pwd=? where name=?";
conn=DBUtil.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, user.getPassword());
ps.setString(2, user.getUsername());
int i= ps.executeUpdate();
if(i==0){
flag=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeAll(null, ps, conn);
}
return flag;
}
//删除数据
public boolean delete(int id){
boolean flag=true;
Connection conn=null;
PreparedStatement ps=null;
String sql="delete from user where id=?";
conn=DBUtil.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, id);
int i=ps.executeUpdate();
if(i==0){
flag=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeAll(null, ps, conn);
}
return flag;
}
//查询数据
public List<User> query(){
List<User> list= new ArrayList<User>();
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String sql="select * from user";
conn=DBUtil.getConn();
try {
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
User user= new User();
user.setUsername(rs.getString("name"));
user.setPassword(rs.getString("pwd"));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBUtil.closeAll(rs, ps, conn);
}
return list;
}
}