创建与数据库的连接
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java
user=root
password=root
package com.hbsi.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCConnection {
static String driver;
static String url;
static String user;
static String password;
static{
//通过类名调动类的加载器获取源的流,InputString是输入流,连接到db.properties的文件中
InputStream in=JDBCConnection.class.getClassLoader().getResourceAsStream("db.properties");
//产生Properties对象,在until包中,代表一个持久的属性集
Properties pr=new Properties();
try {
//load作用:从输入流中读取属性列表
pr.load(in);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//通过getProperty方法传一个key进去,key对应db.properties中定义的
driver=pr.getProperty("driver");
url=pr.getProperty("url");
user=pr.getProperty("user");
password=pr.getProperty("password");
}
public static Connection getConnection(){
Connection con=null;
try {
Class.forName(driver);}
catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
con=DriverManager.getConnection(url, user, password);
//System.out.println("成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public static void closeDB(Connection con,Statement st,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void dbClose1(Connection con,Statement st){
if(st!=null)
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(con!=null)
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
getConnection();
}
}
创建一个对象类
package com.hbsi.doMain;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable{
private Integer id;
private String name;
private String pass;
private Date logintime;
public User(){
super();
}
public User(String name,String pass,Date logintime){
super();
this.name=name;
this.pass=pass;
this.logintime=logintime;
}
public User(Integer id,String name,String pass,Date logintime){
super();
this.id=id;
this.name=name;
this.pass=pass;
this.logintime=logintime;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPass() {
return pass;
}
public void setPass(String pass) {
this.pass = pass;
}
public Date getLogintime() {
return logintime;
}
public void setLogintime(Date logintime) {
this.logintime = logintime;
}
@Override
public String toString() {
return "User [id=" + id + ", logintime=" + logintime + ", name=" + name
+ ", pass=" + pass + "]";
}
}
创建一个接口
package com.hbsi.dao;
import java.util.List;
import com.hbsi.doMain.User;
public interface UserDao {
public User login(String name,String pass);
public boolean insert(User entity);
/**
* 更新实体的操作
*/
public boolean update(User entity);
/**
* 删除实体
*/
public boolean delete(User entity);
/**
* 根据id删除实体对象
*/
public boolean deleteById(Integer id);
/**
* 查询所有的实体对象
*/
public List<User> findAll();
/**
* 根据主键查询具体的某个实体
*/
public User findById(Integer id);
}
编写一个类去实现这个接口
package com.hbsi.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import com.hbsi.doMain.User;
import com.hbsi.util.JDBCConnection;
public class UserDaoImple implements UserDao{
public User login(String name, String pass) {
User user=null;
Connection con=null;
PreparedStatement st=null;
ResultSet rs=null;
try {
con=JDBCConnection.getConnection();
String sql="select id,name,pass,logintime from User where name=? and pass=?";
st=con.prepareStatement(sql);
st.setString(1,name);
st.setString(2,pass);
rs=st.executeQuery();
if(rs.next()){
System.out.print(rs.getInt("id"));
System.out.print("\t"+rs.getString("name"));
System.out.print("\t"+rs.getString("pass"));
System.out.println("\t"+rs.getString("logintime"));
user=new User();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setPass(rs.getString(3));
user.setLogintime(rs.getDate(4));
}
JDBCConnection.closeDB(con, st, rs);
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
return user;
}
public boolean insert(User entity){
boolean flag=false;
Connection con=null;
PreparedStatement ps=null;
try {
con=JDBCConnection.getConnection();
String sql="insert into user(name,pass,logintime) values(?,?,?)";
ps=con.prepareStatement(sql);
int index=1;
ps.setString(index++, entity.getName());
ps.setString(index++,entity.getPass());
ps.setDate(index++, new Date(System.currentTimeMillis()));
int i=ps.executeUpdate();
if(i>0){
flag=true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCConnection.dbClose1(con, ps);
}
return flag;
}
public boolean deleteById(Integer id) {
//第一步:声明返回值变量
boolean flag=false;
//第二步:声明sql语句
String sql="delete from user where id=?";
//第三步:获取连接对象
Connection con=JDBCConnection.getConnection();
//第四步:根据sql语句 获取预处理对象
PreparedStatement ps=null;
try{
ps=con.prepareStatement(sql);
ps.setInt(1,id);
int i=ps.executeUpdate();
if(i>0){
flag=true;
System.out.println("删除成功!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCConnection.dbClose1(con, ps);
}
//第五步:为占位符 赋值
//第六步:执行查询||更新
//第七步:执行判断 if(rs.next()) while(rs.next()) || int n if(n>0)
//第八步:关闭操作
return flag;
}
@Override
public boolean delete(User entity) {
boolean flag=false;
Connection con=null;
PreparedStatement ps=null;
String sql="delete from user where id=?";
try{
ps=con.prepareStatement(sql);
ps.setInt(1,entity.getId());
int i=ps.executeUpdate();
if(i>0){
flag=true;
System.out.println("删除成功!!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCConnection.dbClose1(con, ps);
}
return flag;
}
@Override
public List<User> findAll() {
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
List<User> list=new ArrayList<User>();
try{
con=JDBCConnection.getConnection();
String sql="select id,name,pass,logintime from user";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
User user = new User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPass(rs.getString("pass"));
user.setLogintime(rs.getTimestamp("logintime"));
list.add(user);
}
}catch(Exception e){
e.printStackTrace();
}
return list;
}
@Override
public User findById(Integer id) {
User user=new User();
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
try{
con=JDBCConnection.getConnection();
String sql="select id,name,pass,logintime from user where id=?";
ps=con.prepareStatement(sql);
ps.setInt(1,id);
rs=ps.executeQuery();
if(rs.next()){
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPass(rs.getString("pass"));
user.setLogintime(rs.getDate("logintime"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCConnection.closeDB(con, ps, rs);
}
return user;
}
@Override
public boolean update(User entity) {
boolean flag=false;
Connection con=null;
PreparedStatement ps=null;
con=JDBCConnection.getConnection();
try{
String sql="update user set pass=? where id=?";
ps=con.prepareStatement(sql);
ps.setString(1,entity.getPass());
ps.setInt(2,entity.getId());
int i=ps.executeUpdate();
if(i>0){
flag=true;
System.out.println("修改成功!!");
}
}catch(Exception e){
e.printStackTrace();
}finally{
JDBCConnection.dbClose1(con, ps);
}
return flag;
}
}
编写一个类安全处理
package com.hbsi.servce;
import java.util.List;
import com.hbsi.doMain.User;
//业务处理的接口
public interface UserService {
/**
* 登陆验证的方法
*/
public User login(String name,String pass);
public boolean insert(User entity);
public boolean deleteById(Integer id);
public boolean update(User entity);
public boolean delete(User entity);
public List<User> findAll();
public User findById(Integer id);
}
package com.hbsi.servce;
import java.util.List;
import com.hbsi.dao.UserDao;
import com.hbsi.dao.UserDaoImple;
import com.hbsi.doMain.User;
/**
* 事物的处理
* 安全性
* 等等
* @author Administrator
*
*/
public class UserServiceImpl implements UserService {
//数据库操作的对象
private UserDao userDao = new UserDaoImple();
public User login(String name, String pass)
{
//完成之前:我可以进行验证 这个账号 是不是合法。
User entity = userDao.login(name, pass);
//完成安全的加密等等
return entity;
}
public boolean insert(User entity) {
return userDao.insert(entity);
}
public boolean deleteById(Integer id){
return userDao.deleteById(id);
}
@Override
public boolean update(User entity) {
return userDao.update(entity);
}
@Override
public boolean delete(User entity) {
// TODO Auto-generated method stub
return userDao.delete(entity);
}
public List<User> findAll(){
return userDao.findAll();
}
public User findById(Integer id){
return userDao.findById(id);
}
}
编相应的servlet
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String userName=request.getParameter("username");
String userPass=request.getParameter("userpass");
//String name = new String(userName.getBytes("ISO-8859-1"), "UTF-8");
//String pass = new String(userPass.getBytes("ISO-8859-1"), "UTF-8");
System.out.println(userName+"....."+userPass);
User entity = userDao.login(userName, userPass);
if(entity==null){
response.sendRedirect("./error.jsp");
//System.out.println("用户名或密码错误");
}else{
request.setAttribute("user",entity);
request.getRequestDispatcher("./sc.jsp").forward(request, response);
//System.out.println("登陆成功");
}
if (userName == null || userName.equals("")) {
System.out.println("请输入姓名!");
return;
}
if (userPass == null || userPass.equals("")) {
System.out.println("请输入密码!");
return;
}
}
插入:
private UserService userService = new UserServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String userName=request.getParameter("username");
String userPass=request.getParameter("userpass");
User entity=new User(userName,userPass,null);
boolean flag=userService.insert(entity);
if(flag){
//转发
request.setAttribute("flag",true);
request.getRequestDispatcher("/sc2.jsp").forward(request, response);
//System.out.println("成功!");
}
}
更新:
private UserService userService = new UserServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setCharacterEncoding("UTF-8");
Integer id=Integer.valueOf(request.getParameter("id"));
String oldPass=request.getParameter("pass");
String newPass=request.getParameter("newpass");
User entity=new User(id,null,newPass,null);
boolean flag = userService.update(entity);
if (flag) {
JOptionPane.showMessageDialog(null,"修改成功");
}
}
按实体查询
private UserService userService = new UserServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter pw = response.getWriter();
// 与数据库比较,判断用户是否存在
List<User> list = userService.findAll();
pw.write("<h1>查询内容是:</h1>");
for (User user : list) {
pw.write("id:" + user.getId() + "<br>");
pw.write("用户名:" + user.getName() + "<br>");
pw.write("密码:" + user.getPass() + "<br>");
pw.write("登录时间:" + user.getLogintime() + "<br>");
response.getWriter().write("<h3>下一个用户是:</h3>");
}
}
按id查询:
private UserService userServce=new UserServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out=response.getWriter();
Integer id=Integer.valueOf(request.getParameter("id"));
User user=userServce.findById(id);
response.getWriter().write("<h1>查询内容是:</h1>");
out.write("id:" + user.getId() + "<br>");
out.write("用户名:" + user.getName() + "<br>");
out.write("密码:" + user.getPass() + "<br>");
out.write("登录时间:" + user.getLogintime());
}
按实体删除:
private UserService userService = new UserServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setCharacterEncoding("UTF-8");
Integer id=Integer.valueOf(request.getParameter("id"));
String name=request.getParameter("username");
User entity = new User();
boolean flag = userService.delete(entity);
if (flag) {
JOptionPane.showMessageDialog(null, "删除成功");
}
}
按id删除:
private UserService userService = new UserServiceImpl();
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
Integer id=Integer.valueOf(request.getParameter("id"));
boolean flag=userService.deleteById(id);
if(flag){
JOptionPane.showMessageDialog(null, "删除成功");
}
}