学习了JDBC,进行数据库连接的简单代码
JDBC连接数据库七步骤
1.载入JDBC驱动
2.定义连接URL
3.建立连接
4.创建PreparedStatement对象
5.建立查询或更新
6.结果处理
7.关闭连接
- 工具类连接数据库
public class DbUtils {
private static final String driver="org.gjt.mm.mysql.Driver";
private static final String url="jdbc:mysql://localhost:3306/testdb?&useUnicode=true&characterEncoding=utf-8";
private static final String user="root";
private static final String pwd="";
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConn(){
try {
return DriverManager.getConnection(url,user,pwd);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(Connection conn){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(PreparedStatement pstm){
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- 实体类User类
public class User {
private int id;
private String name;
private String pwd;
public int setId(int id ){
return this.id=id;
}
public int getId(){
return id;
}
public String setName(String name){
return this.name=name;
}
public String getName(){
return name;
}
public String setPwd(String pwd){
return this.pwd=pwd;
}
public String getPwd(){
return pwd;
}
@Override
public String toString() {
return "User [id=" + id + ", name=" + name + ", pwd=" + pwd + "]";
}
}
- Dao层接口
public interface UserDao {
boolean add(User u);
boolean del(int id);
boolean modify(User u);
User queryByNamePwd(String UserName,String UserPwd);
List<User> queryAll();
}
- Dao层接口实现类
public class UserDaoImpl implements UserDao{
@Override
public boolean add(User u) {
Connection conn=null;
PreparedStatement pstm=null;
try {
conn=DbUtils.getConn();
String sql=" insert into user(name,pwd) values(?,?) ";
pstm=conn.prepareStatement(sql);
pstm.setObject(1,u.getName());
pstm.setObject(2,u.getPwd());
int r=pstm.executeUpdate();
return r>0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}finally{
DbUtils.close(pstm);
DbUtils.close(conn);
}
}
@Override
public boolean del(int id) {
Connection conn=null;
PreparedStatement pstm=null;
try {
conn=DbUtils.getConn();
String sql=" delete from user where id=? ";
pstm=conn.prepareStatement(sql);
pstm.setObject(1, id);
int r=pstm.executeUpdate();
return r>0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}finally{
DbUtils.close(pstm);
DbUtils.close(conn);
}
}
@Override
public boolean modify(User u) {
Connection conn=null;
PreparedStatement pstm=null;
try {
conn=DbUtils.getConn();
String sql=" update user set name=?,pwd=? where id=? ";
pstm=conn.prepareStatement(sql);
pstm.setObject(1,u.getName());
pstm.setObject(2,u.getPwd());
pstm.setObject(3,u.getId());
int r=pstm.executeUpdate();
return r>0;
} catch (SQLException e) {
e.printStackTrace();
return false;
}finally{
DbUtils.close(pstm);
DbUtils.close(conn);
}
}
@Override
public User queryByNamePwd(String UserName,String UserPwd) {
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try {
conn=DbUtils.getConn();
String sql =" select * from user where name=? and pwd=? ";
pstm=conn.prepareStatement(sql);
pstm.setObject(1,UserName);
pstm.setObject(2,UserPwd);
rs=pstm.executeQuery();
User user =null;
while(rs.next()){
user=new User();
int id=rs.getInt(1);
String name=rs.getString(2);
String pwd=rs.getString(3);
user.setId(id);
user.setName(name);
user.setPwd(pwd);
}
return user;
} catch (SQLException e) {
e.printStackTrace();
return null;
}finally{
DbUtils.close(rs);
DbUtils.close(pstm);
DbUtils.close(conn);
}
}
@Override
public List<User> queryAll() {
List<User> users=new ArrayList<>();
Connection conn=null;
PreparedStatement pstm=null;
ResultSet rs=null;
try {
conn=DbUtils.getConn();
String sql=" select * from user ";
pstm=conn.prepareStatement(sql);
rs=pstm.executeQuery();
while(rs.next()){
User user=new User();
int id=rs.getInt(1);
String name=rs.getString(2);
String pwd= rs.getString(3);
user.setId(id);
user.setName(name);
user.setPwd(pwd);
users.add(user);
}
return users;
} catch (SQLException e) {
e.printStackTrace();
return null;
}finally{
DbUtils.close(rs);
DbUtils.close(pstm);
DbUtils.close(conn);
}
}
}
- 服务层接口
public interface UserService {
boolean add(User u);
boolean del(int id);
boolean modify(User u);
User queryByNamePwd(String UserName,String UserPwd);
List<User> queryAll();
}
- 服务层接口实现类
public class UserServiceImpl implements UserService{
UserDao user=new UserDaoImpl();
@Override
public boolean add(User u) {
return user.add(u);
}
@Override
public boolean del(int id) {
return user.del(id);
}
@Override
public boolean modify(User u) {
return user.modify(u);
}
@Override
public User queryByNamePwd(String UserName, String UserPwd) {
return user.queryByNamePwd(UserName, UserPwd);
}
@Override
public List<User> queryAll() {
return user.queryAll();
}
}
- 简单测试
User user=new User();
UserService users=new UserServiceImpl();
user.setId(9);
user.setName("wng");
user.setPwd("1589");
System.out.println(users.modify(user));