jsp dbbean mysql_jsp中对MySql的增删查改操作

1.配置数据源与数据池

在Web应用程序中建立一个META-INF目录，在其中建立一个context.xml文件。

//传递给JDBC驱动程序的数据库URL

2.进行连接数据库，这里为什么要单独写一个文件呢？因为一般采用DAO模式设计，在这个模式中，数据库访问与应用程序中实现业务逻辑是分开的。

import javax.naming.Context;

import javax.naming.InitialContext;

import javax.naming.NamingException;

import javax.sql.DataSource;

import java.sql.*;

public class DBBean{

Connection con;

DataSource dataSource;

public DBBean() throws NamingException, SQLException {

Context context = new InitialContext();

dataSource = (DataSource)context.lookup("java:comp/env/jdbc/sampleDS");

con = dataSource.getConnection();

}

public Connection getConnection() throws NamingException, SQLException {

return con;

}

}

3.实现数据的增添

Connection con = null;

ArrayListstudents = (ArrayList)request.getParameter("value"); //获取要插入的数据

student std = new student(); //把表的数据放到student类里

try {

DBBean dao = new DBBean(); //链接数据库

con = dao.getConnection();

} catch (NamingException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

String sql = "INSERT INTO student" + "(id, name, sex, major, hometown)VALUES(?,?,?,?,?)";

try(

PreparedStatement pstmt = con.prepareStatement(sql)) {

for(student std:students) {

pstmt.setString(1, std.getId());

pstmt.setString(2, std.getName());

pstmt.setString(3, std.getSex());

pstmt.setString(4, std.getMajor());

pstmt.setString(5, std.getHometown());

pstmt.executeUpdate();

}

}

catch (SQLException e) {

e.printStackTrace();

}

4.实现数据的删除

Connection con = null;

String s = (String)request.getParameter("value"); //要删除的数据的表的第一个值，同时也是主键

student std = new student(); //把表的数据放到student类里

try {

DBBean dao = new DBBean(); //链接数据库

con = dao.getConnection();

} catch (NamingException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

String sql = "DELETE FROM student where id = "+ s;

try(

PreparedStatement pstmt = con.prepareStatement(sql)){

pstmt.executeUpdate();

}catch(SQLException e) {

e.printStackTrace();

}

5.实现数据的查找

Connection con = null;

try {

DBBean dao = new DBBean(); //链接数据库

con = dao.getConnection();

} catch (NamingException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

ArrayListstdList = new ArrayList();

String sql = "SELECT * FROM student";

try (

PreparedStatement pstmt = con.prepareStatement(sql);

ResultSet rst = pstmt.executeQuery()){

while(rst.next()){

student std = new student();

std.setId(rst.getString("id"));

std.setName(rst.getString("name"));

std.setSex(rst.getString("sex"));

std.setMajor(rst.getString("major"));

std.setHometown(rst.getString("hometown"));

stdList.add(std);

}

}catch(SQLException e){

e.printStackTrace();

}

嘿嘿，我知道你也想要单个的查找

Connection con = null;

String s = (String)request.getParameter("value"); //要查找的数据的表的第一个值，同时也是主键

student std = new student(); //把表的数据放到student类里

try {

DBBean dao = new DBBean(); //链接数据库

con = dao.getConnection();

} catch (NamingException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

String sql = "SELECT id,name,sex,major,hometown" +

" FROM student WHERE id=?";

try(

PreparedStatement pstmt = con.prepareStatement(sql)){

pstmt.setString(1, s);

try(ResultSet rst = pstmt.executeQuery()){

if(rst.next()){

std.setId(rst.getString("id"));

std.setName(rst.getString("name"));

std.setSex(rst.getString("sex"));

std.setMajor(rst.getString("major"));

std.setHometown(rst.getString("hometown"));

}

}

}catch(SQLException e) {

e.printStackTrace();

}

6.实现数据的更新

Connection con = null;

String s = (String)request.getParameter("value"); //要修改的数据的表的第一个值，同时也是主键

student std = new student(); //把表的数据放到student类里

try {

DBBean dao = new DBBean(); //链接数据库

con = dao.getConnection();

} catch (NamingException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

String sql = "SELECT id,name,sex,major,hometown" +

" FROM student WHERE id=?";

try(

PreparedStatement pstmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE)){

pstmt.setString(1, s);

try(ResultSet rst = pstmt.executeQuery()){

if(rst.next()){

//rst.updateString(1, request.getParameter("id"));

rst.updateString(2, request.getParameter("name"));

rst.updateString(3, request.getParameter("sex"));

rst.updateString(4, request.getParameter("major"));

rst.updateString(5, request.getParameter("hometown"));

rst.updateRow();

}

}

}catch(SQLException e) {

e.printStackTrace();

}

怎么说呢，其实这些都应该封装到一个Dao文件，具体的操作看你自己啦