单表增删改查

return regTime; } public void setRegTime(String regTime) {
this.regTime = regTime; }
public int getUid() { return uid;
} public void setUid(int uid) {
this.uid = uid; } public String getUname() {
import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList;
import net.xabc.users.db.BaseDao; import net.xabc.users.entity.Users;
单表增删改查
第一步：创建数据库以数据库表 create database users go use users go
create table users (
uid int not null primary key identity(1,1), uname varchar(50) not null unique, usex varchar(10) not null, regTime datetime default getDate() ) go
public class UserDao extends BaseDao{
private Connection conn; private PreparedStatement pstmt; private ResultSet res; /**
* 查询方法
* */ public ArrayList queryUser(String sql) {
insert into users(uname,usex)values('linder','男') insert into users(uname,usex)values('jack','男') insert into users(uname,usex)values('tom','男') insert into users(uname,usex)values('rose','女') insert into users(uname,usex)values('jhon','男') insert into users(uname,usex)values('pack','男') insert into users(uname,usex)values('mary','女') go
public class BaseDao {
private static final String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver"; private static final String URL="jdbc:sqlserver://localhost:1434;DatabaseName=users"; private static final String USERS="sa"; private static final String PWD="linder";
/** * 增加方法 * */ public int addUserBiz(Users u) {
String sql = "insert into users(uname,usex)values('" + u.getUname() + "','" + u.getUsex() + "')";
int result = dao.ADUUsers(sql); return result; } /** * 删除方法 * */ public int deleteUserBiz(int uid) { String sql = "delete from users where uid=" + uid; int result = dao.ADUUsers(sql); return result; } /** * 修改方法 * */ public int updateUserBiz(Users u) { String sql = "update users set uname='" + u.getUname() + "',usex='"
}catch(Exception e){ System.out.println("获取数据库连接异常 ："+e.getMessage()); return null;
} }
/** * 释放资源 * */ public void closeAll(Connection conn,PreparedStatement pstmt,ResultSet res) {
public class UsersBiz {
UserDao dao = new UserDao();
/** * 用于首页全部查询 * */ public ArrayList queryUserBiz() {
ArrayList list = new ArrayList(); String sql = "select * from users"; list = dao.queryUser(sql); return list; }
try {
if(pstmt!=null)
{ pstmt.close(); pstmt=null;
} }catch(Exception e) {
System.out.println("关闭PreparedStatement异常:"+e.getMessage()); } }
private void closeResultSet(ResultSet res) {
this.closeResultSet(res); this.closePreparedStatement(pstmt); this.closeConnection(conn); }
private void closeConnection(Connection conn) {
try {
if(!conn.isClosed()&& conn!=null) {
private Connection conn=null;
/** * 获取数据库连接 * */ public Connection getConnection() {
try
{ //加载驱动 Class.forName(DRIVER); //获取数据库连接 conn=DriverManager.getConnection(URL, USERS, PWD); return conn;
conn.close(); conn=null; } }catch(Exception e) { System.out.println("关闭数据库连接异常："+e.getMessage()); } } private void closePreparedStatement(PreparedStatement pstmt) {
/** * 用于修改信息，根据uid查询一条信息 * */ public ArrayList queryUserBiz(int id) {
ArrayList list = new ArrayList(); String sql = "select * from users where uid=" + id; list = dao.queryUser(sql); return list; }
super.closeAll(conn, pstmt, null); } return result; } }
第六步：创建业务类 BIZ package net.xabc.users.biz;
import java.util.ArrayList;
import net.xabc.users.dao.UserDao; import net.xabc.users.entity.Users;
try {
if(res!=null) {
res.close(); res=null; } }catch(Exception e) { System.out.println("关闭结果接异常:"+e.getMessage()); } } }
第五步：创建业务 DAO package net.xabc.users.dao;
select * from users go
第二步：写实体 package net.xabc.users.entity;
public class Users {
private int uid; private String uname; private String usex; private String regTime; public String getRegTime() {
return uname; } public void setUname(String uname) {
this.uname = uname; } public String getUsex() {
return usex; } public void setUsex(String usex) {
this.usex = usex; } }
第三步：写数据库连接类 BaseDao pቤተ መጻሕፍቲ ባይዱckage net.xabc.users.db;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet;
+ u.getUsex() + "' where uid=" + u.getUid(); int result = dao.ADUUsers(sql); return result; } }
第六步：创建页面 1、index.jsp 显示全部查询结果 <%@ page language="java" pageEncoding="GB18030"%> <%@ page import="net.xabc.users.biz.UsersBiz" %> <%@ page import="net.xabc.users.entity.Users" %> <%@ page import="java.util.ArrayList" %>
ArrayList list=new ArrayList(); try {
conn=super.getConnection(); pstmt=conn.prepareStatement(sql); res=pstmt.executeQuery(); while(res.next()) {
Users u=new Users(); u.setUid(res.getInt("uid")); u.setUname(res.getString("uname")); u.setUsex(res.getString("usex")); u.setRegTime(res.getString("regTime")); list.add(u); } return list; }catch(Exception e) { System.out.println("查询用户异常："+e.getMessage()); return null; }finally { super.closeAll(conn, pstmt, res); } } /** * 执行增加查询修改方法 * */ public int ADUUsers(String sql) { int result=0; try{ conn=super.getConnection(); pstmt=conn.prepareStatement(sql); result=pstmt.executeUpdate(); }catch(Exception e) { System.out.println("增加修改删除用户异常:"+e.getMessage()); result=-1; }finally {