servlet mysql修改功能_servlet中MySQL数据库的增、删、查、改

//添加user

public void addUser(User user) throws Exception{

//通过tomcat连接池获得数据库的连接

Connection conn = null;

DataSource ds = null;

Context initCtx;

try {

initCtx = new InitialContext();

ds =(DataSource)initCtx.lookup("java:comp/env/jdbc/test");

if(ds!=null){

conn = ds.getConnection();

}

} catch (NamingException e) {

e.printStackTrace();

}

String sql=""+

"insert into account"+

"(name,password)"+

"values("+

"?,?)";

//2.ͨ通过数据库的连接操作数据库

PreparedStatement ptmt=conn.prepareStatement(sql);

ptmt.setString(1,user.getName());

ptmt.setString(2,user.getPassword());

ptmt.execute();

//3.添加语句

//ResultSet rs=stmt.executeQuery("insert into account('name','password')value('wocao','12345')");

}

//更改user

public void updateUser(User user) throws SQLException{

//通过tomcat连接池获得数据库的连接

Connection conn = null;

DataSource ds = null;

Context initCtx;

try {

initCtx = new InitialContext();

ds =(DataSource)initCtx.lookup("java:comp/env/jdbc/test");

if(ds!=null){

conn = ds.getConnection();

}

} catch (NamingException e) {

e.printStackTrace();

}

String sql=""+

" update account"+

" set name=?,password=?  "+

" where id=? ";

//2.ͨ通过数据库的连接操作数据库

PreparedStatement ptmt=conn.prepareStatement(sql);

ptmt.setString(1,user.getName());

ptmt.setString(2, user.getPassword());

ptmt.setInt(3,user.getId());

ptmt.execute();

}

//删除user

public void delUser(Integer id) throws SQLException{

//通过tomcat连接池获得数据库的连接

Connection conn = null;

DataSource ds = null;

Context initCtx;

try {

initCtx = new InitialContext();

ds =(DataSource)initCtx.lookup("java:comp/env/jdbc/test");

if(ds!=null){

conn = ds.getConnection();

}

} catch (NamingException e) {

e.printStackTrace();

}

String sql=""+

" delete from account "+

" where id=? ";

//2.通过数据库的连接操作数据库

PreparedStatement ptmt=conn.prepareStatement(sql);

ptmt.setInt(1, id);

ptmt.execute();

}

//查询user

public List query() throws Exception{

//通过tomcat连接池获得数据库的连接

Connection conn = null;

DataSource ds = null;

Context initCtx = new InitialContext();

ds =(DataSource)initCtx.lookup("java:comp/env/jdbc/test");

if(ds!=null){

conn = ds.getConnection();

}

//2.通过连接池操作数据库

Statement stmt= conn.createStatement();

//3.查询语句

ResultSet rs=stmt.executeQuery("select * from account");

//4..创建一个将要返回的List集合对象list，一个User的对象u

List list=new ArrayList();

User u = null;

//5.重设用户User对象u中的属性，然后将重设后的对象u添加到集合对象list中

while(rs.next()){

u=new User();

u.setId(rs.getInt("id"));

u.setName(rs.getString("name"));

u.setPassword(rs.getString("password"));

list.add(u);

}

return list;

}

public User get(Integer id) throws SQLException{

//通过tomcat连接池获得数据库的连接

User user=null;

Connection conn = null;

DataSource ds = null;

PreparedStatement ptmt=null;

ResultSet rs=null;

Context initCtx;

try {

initCtx = new InitialContext();

ds =(DataSource)initCtx.lookup("java:comp/env/jdbc/test");

if(ds!=null){

conn = ds.getConnection();

}

String sql=""+

" select * from account"+

" where id=? ";

//2.通过连接池的连接操作数据库

ptmt=conn.prepareStatement(sql);

ptmt.setInt(1,id);

rs=ptmt.executeQuery();

//conn.close();

hile(rs.next()){

user=new User();

user.setId(rs.getInt("id"));

user.setName(rs.getString("name"));

user.setPassword(rs.getString("password"));

}

} catch (NamingException e) {

e.printStackTrace();

}finally{

rs.close();

ptmt.close();

conn.close();

}

return user;

}