实践出真知看实例：
步骤
1.首先建库建表为连接做准备
2.建web项目导入sqlServer jar包
3.创建Util工具类根据表新建javabean
4.编写查询方法
5.整合代码
详解 ⬇⬇⬇⬇⬇⬇
1.建库建表为连接做准备
--建库
create database Demo
on(
name='StuMessage',
filename ='D:\Demo.mdf',
size=10mb,
filegrowth=10%
)
--建表
use Demo
create table users(
id int,
name varchar(36),
sex varchar(8)
)
2.jar包
3.创建Util工具类根据表新建javabean
1.创建Util工具类
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Util {
/**
* 获取Connection
* @return
*/
public Connection getConn(){
Connection conn = null;
try {
//驱动包位置
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
System.out.println("驱动加载失败");
e.printStackTrace();
}
try {
//sqlServer连接和数据库名
String url ="jdbc:sqlserver://localhost:1433;databaseName=Demo";
String user ="sa";//数据库账号
String pwd = "1";//数据库密码
conn=DriverManager.getConnection(url,user,pwd);
} catch (SQLException e) {
System.out.println("数据库连接失败");
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("连接成功");
return conn;
}
/**
* 关闭资源
* @param conn
* @param st
* @param rs
*/
public void close(Connection conn , PreparedStatement st ,ResultSet rs){
try {
if(conn!=null){conn.close();}
if(st!=null){st.close();}
if(rs!=null){rs.close();}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
2.根据表创建javaBean
public class Users {
private int id ;
private String name ;
private String sex ;
public Users() {
super();
// TODO Auto-generated constructor stub
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
4.编写查询方法
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import cn.msg.bean.Users;
import cn.msg.util.Util;
public class UserServers {
private PreparedStatement ps = null;
private Connection conn = null;
private ResultSet rs = null;
private Util util = new Util();
/**
* 获取一条信息
* @param id
* @return Users
*/
public Users getUser(int id){
String sql = "select * from Users Where id = ? ";
conn = util.getConn();
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs=ps.executeQuery();
if(rs.next()){
Users user = new Users();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setSex(rs.getString(3));
return user;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
util.close(conn, ps, rs);
}
Users user = new Users();
user.setId(0);
user.setName("无此条信息");
user.setSex("无此条信息");
return user;
}
/**
* 获取Users表
* @param table
* @return ArrayList<Users>
*/
public ArrayList<Users> getUsers(){
ArrayList<Users> list = new ArrayList<Users>();
String sql = "select * from users";
conn=util.getConn();
try {
ps=conn.prepareStatement(sql);
//ps.setString(1, table);
rs=ps.executeQuery();
while(rs.next()){
Users user = new Users();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setSex(rs.getString(3));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//若表中没有信息
if(list.size()==0){
Users user = new Users();
user.setId(-1);
user.setName("表中无信息");
user.setSex("表中无信息");
list.add(user);
}
return list;
}
/**
*添加一条信息
* @param id
* @param name
* @param sex
* @return返回true添加成功false添加失败
*/
public Boolean insert(int id , String name , String sex){
String sql = "insert into users values(?,?,?)";
conn = util.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, id);
ps.setString(2, name);
ps.setString(3, sex);
if(ps.executeUpdate()>0){
return true;
}else{
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
* 根据id删除一条信息
* @param id
* @return
*/
public Boolean delete(int id){
String sql = "delete from users where id=?";
conn = util.getConn();
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
if(ps.executeUpdate()>0){
return true;
}else{
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
util.close(conn, ps, rs);
}
return null;
}
/**
* 根据id更新数据
* @param id
* @param name
* @param sex
* @return
*/
public Boolean update(int id , String name , String sex){
String sql = "update users set name = ?,sex = ? where id = ?";
conn = util.getConn();
try {
ps=conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, sex);
ps.setInt(3, id);
if(ps.executeUpdate()>0){
return true;
}else{
return false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
}
5.整合代码
1.jsp页面代码
<body>
<%
UserServers stu = new UserServers();
ArrayList<Users> list = new ArrayList<Users>();
list = stu.getUsers();
%>
<h1>数据库增删改查</h1>
<br>
添加信息:
<form action="servlet/insert">
id:<input type="number" name="id">
name:<input type="text" name = "name">
sex:<input type = "text" name = "sex">
<button>确认</button><br>
</form>
更新信息（根据id更新）:
<form action="servlet/update">
id:<input type="number" name="id">
name:<input type="text" name = "name">
sex:<input type = "text" name = "sex">
<button>确认</button><br>
</form>
根据ID删除信息：
<form action="servlet/delete">
<input type="number" name="delete"><button onclick="">确认</button><br>
</form>
<h1>信息显示</h1>
<table>
<tr><th>ID</th><th>Name</th><th>Sex</th></tr>
<%for(Users user : list){
%>
<tr>
<td><%=user.getId() %></td>
<td><%=user.getName() %></td>
<td><%=user.getSex() %></td>
<%
} %>
</table>
</body>
由jsp代码可以看出做不同的操作需要用不同的servlet做后台处理输入的信息
2.对添加信息做支持的Servlet
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import cn.msg.servers.UserServers;
public class insert extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");//防止乱码
response.setContentType("text/html");
PrintWriter out = response.getWriter();
UserServers stu = new UserServers();//实例化刚刚编写的查询方法
//获取传值
int id = Integer.parseInt(request.getParameter("id"));
String name = new String(request.getParameter("name").getBytes("ISO-8859-1"),"utf-8");
String sex = new String(request.getParameter("sex").getBytes("ISO-8859-1"),"utf-8");
//调用插入信息的方法
stu.insert(id, name, sex);
//重定向到主页面
response.sendRedirect("http://127.0.0.1:8080/Demo10.15/");
out.flush();
out.close();
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
3.对更新信息做支持的Servlet
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.msg.servers.UserServers;
public class update extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html");
PrintWriter out = response.getWriter();
UserServers stu = new UserServers();//实例化刚刚编写的查询方法类
//接收传值
int id = Integer.parseInt(request.getParameter("id"));
String name = new String(request.getParameter("name").getBytes("ISO-8859-1"),"utf-8");
String sex = new String(request.getParameter("sex").getBytes("ISO-8859-1"),"utf-8");
//调用更新方法
stu.update(id, name, sex);
//重定向到主页面
response.sendRedirect("http://127.0.0.1:8080/Demo10.15/");
out.flush();
out.close();
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
4.对删除信息做支持的Servlet
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.msg.servers.UserServers;
public class delete extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
UserServers stu = new UserServers();
int id = Integer.parseInt(request.getParameter("delete"));
System.out.println(id);
stu.delete(id);
response.sendRedirect("http://127.0.0.1:8080/Demo10.15/");
out.flush();
out.close();
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
转载：https://blog.csdn.net/qq_44784185/article/details/102567327
查看评论