package com.weihai.conn;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBconn {
public static final String dbDriver="com.mysql.jdbc.Driver";
public static final String dbURL="jdbc:mysql://localhost:3306/db_shop?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
public static final String dbUsername="root";
public static final String dbPassword="1234";
public static Connection conn = null;
public PreparedStatement pt = null;
public ResultSet rs = null;
//创建数据库连接
public static Connection getConntion() {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(dbURL, dbUsername, dbPassword);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//关闭数据库连接
public static void close(Connection conn,PreparedStatement pt,ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (pt != null) {
try {
pt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(Connection conn,PreparedStatement pt) {
if (pt != null) {
try {
pt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
以上封装了连接数据库关闭数据库的代码,在需要的地方直接调用即可
package com.soft1.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.soft1.bean.User;
import com.weihai.conn.*;
//访问数据库的JavaBean
public class UserDao {
public Connection conn = null;
public PreparedStatement pt = null;
public ResultSet rs = null;
//实现用户名密码验证
public Boolean denglu(String username, String password) {
Boolean t=false;
conn=DBconn.getConntion();
String sql="select * from admin_info where name=? and password=?";
try {
pt=conn.prepareStatement(sql);
pt.setString(1, username);
pt.setString(2, password);
rs = pt.executeQuery();
if (rs.next()) {
t= true;
} else {
t=false;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBconn.close(conn,pt,rs);
return t;
}
//查询
public ArrayList<User> queryAll(){
//使用泛型,解决强转的问题
ArrayList<User> users = new ArrayList<User>();
//2.访问数据库,查询用户名密码
//(1)加载数据库驱动
conn=DBconn.getConntion();
//(3)创建语句容器
String sql= "select * from admin_info";
try {
pt = conn.prepareStatement(sql);
//(4)执行SQL语句
rs = pt.executeQuery();
//遍历结果集,把结果集转存到users中
while(rs.next()){
//一条记录对应一个User对象
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
//需要把每个user添加到Users中
users.add(user);
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
//关闭数据库连接
DBconn.close(conn,pt,rs);
return users;
}
//添加
public boolean insert(User user) {
boolean t=false;
conn=DBconn.getConntion();
String sql="insert into admin_info(name,password) values(?,?)";
try {
pt=conn.prepareStatement(sql);
pt.setString(1, user.getUsername());
pt.setString(2, user.getPassword());
//执行SQL语句
int i=pt.executeUpdate();
System.out.println(i);
if(i>0) {
t=true;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBconn.close(conn,pt);
return t;
}
}
以上是封装的登录、查询、添加,
从jsp代码中摘出它们有利于提高代码的可读性,修改等操作也会变得方便许多