Java使用jdbc连接MySql数据库,实现增删改查
首先,导入MySql连接数据库的jar包,我用的是
DB类,用于获得数据库连接
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class DB {
//数据库的URL
private static final String URL="jdbc:mysql://127.0.0.1:3306/test";
//数据库连接的用户名
private static final String USER="root";
//密码
private static final String PASSWORD="123456";
private static Connection conn=null;
static {
try {
//加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//获得数据库的连接
conn=(Connection) DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
return conn;
}
}
实体类Users:
public class Users {
private int id;
private String username;
private String password;
public Users() {
super();
}
public int getId() {
return id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Users(String username, String password) {
this.username = username;
this.password = password;
}
}
数据库操作类UsersDao,实现users的增删改查操作
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
//导入刚才的两个类
import cn.jufe.sex.db.DB;
import cn.jufe.sex.model.Users;
public class UsersDao {
public boolean addUser(Users user) throws SQLException{
//增加用户,传入一个users对象,将对象中的数据加入数据库
//id属性我在数据中设置了自增长型,所以ID不用去设置
//获得数据库连接
Connection conn = DB.getConnection();
//sql语句
String sql ="insert into users(username,password) "+ "values(?,?)";
//通过conn获得PreparedStatement对象,该对象可对sql语句进行预编译
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
//对sql语句中的参数进行赋值
ptmt.setString(1, user.getUsername());
ptmt.setString(2, user.getPassword());
//执行SQL语句,返回受影响的行数,a大于0,则成功
int a = ptmt.executeUpdate();
if(a>0)
return true;
else
return false;
}
public boolean deluser(Integer id) throws SQLException{//删
//获得数据库连接
Connection conn = DB.getConnection();
//sql语句
String sql ="delete from users where id=?";
//通过conn获得PreparedStatement对象,该对象可对sql语句进行预编译
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setInt(1, id);
//执行SQL语句,返回受影响的行数,a大于0,则成功
int a = ptmt.executeUpdate();
if(a>0)
return true;
else
return false;
}
public boolean updateuser(Users user) throws SQLException{//改
//获得数据库连接
Connection conn = DB.getConnection();
//sql语句
String sql =" update users set name=?,password=? where id=?";
//通过conn获得PreparedStatement对象,该对象可对sql语句进行预编译
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1, u.getName());
ptmt.setString(2, u.getPassword());
ptmt.setString(3, u.getId());
//执行SQL语句,返回受影响的行数,a大于0,则成功
int a = ptmt.executeUpdate();
if(a>0)
return true;
else
return false;
}
public Users findByName(String username) throws SQLException{
//通过姓名查询用户,通过ID同理
//获得数据库连接
Connection conn=DB.getConnection();
//sql语句
String sql="select * from users where username=?";
//通过conn获得PreparedStatement对象,该对象可对sql语句进行预编译
PreparedStatement ptmt=(PreparedStatement) conn.prepareStatement(sql);
ptmt.setString(1, username);
//new一个Resultset对象接受返回的数据
ResultSet rs=ptmt.executeQuery();
//通过users对象接受rs中的数据,如果rs中含有多个数据则使用数组接收
//while(rs.next()) {
// Users user=new Users();
// user.setPassword(rs.getString("password"));
// user.setUsername(rs.getString("username"));
// list.add(user);
//}
// return list;
while(rs.next()) {
Users user=new Users();
user.setPassword(rs.getString("password"));
user.setUsername(rs.getString("username"));
}
return user;
}
}
UsersDao的使用,直接new一个UsersDao对象,调用函数即可
网友评论