1.配置数据源与数据池
在Web应用程序中建立一个META-INF目录,在其中建立一个context.xml文件。
<?xml version="1.0" encoding="utf-8"?>
<Context reloadable = "true">
<Resource
name="jdbc/sampleDS" //数据源名
type="javax.sql.DataSource" //指定该资源的类型
maxActive="4" //同时活动连接实例的最大数
maxIdle="2" //可空闲的最大连接数
username="root" //数据库用户名
maxWait="5000" //在没有可用连接的情况下,抛出异常前等待的最大毫秒数
driverClassName="com.mysql.jdbc.Driver" //使用的JDBC驱动程序完整的类名
password="123" //数据库密码
url="jdbc:mysql://localhost:3306/mydata"/> //传递给JDBC驱动程序的数据库URL
</Context>
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;
ArrayList<student> students = (ArrayList<student>)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();
}
ArrayList<student> stdList = 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文件,具体的操作看你自己啦