Java使用jdbc連接MySql數(shù)據(jù)庫版确,實現(xiàn)增刪改查
首先趋箩,導入MySql連接數(shù)據(jù)庫的jar包矫户,我用的是
DB類微驶,用于獲得數(shù)據(jù)庫連接
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.Connection;
public class DB {
//數(shù)據(jù)庫的URL
private static final String URL="jdbc:mysql://127.0.0.1:3306/test";
//數(shù)據(jù)庫連接的用戶名
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");
//獲得數(shù)據(jù)庫的連接
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;
}
}
數(shù)據(jù)庫操作類UsersDao腮鞍,實現(xiàn)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對象弛房,將對象中的數(shù)據(jù)加入數(shù)據(jù)庫
//id屬性我在數(shù)據(jù)中設置了自增長型道盏,所以ID不用去設置
//獲得數(shù)據(jù)庫連接
Connection conn = DB.getConnection();
//sql語句
String sql ="insert into users(username,password) "+ "values(?,?)";
//通過conn獲得PreparedStatement對象,該對象可對sql語句進行預編譯
PreparedStatement ptmt = (PreparedStatement) conn.prepareStatement(sql);
//對sql語句中的參數(shù)進行賦值
ptmt.setString(1, user.getUsername());
ptmt.setString(2, user.getPassword());
//執(zhí)行SQL語句,返回受影響的行數(shù)文捶,a大于0荷逞,則成功
int a = ptmt.executeUpdate();
if(a>0)
return true;
else
return false;
}
public boolean deluser(Integer id) throws SQLException{//刪
//獲得數(shù)據(jù)庫連接
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);
//執(zhí)行SQL語句,返回受影響的行數(shù)粹排,a大于0种远,則成功
int a = ptmt.executeUpdate();
if(a>0)
return true;
else
return false;
}
public boolean updateuser(Users user) throws SQLException{//改
//獲得數(shù)據(jù)庫連接
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());
//執(zhí)行SQL語句,返回受影響的行數(shù)顽耳,a大于0坠敷,則成功
int a = ptmt.executeUpdate();
if(a>0)
return true;
else
return false;
}
public Users findByName(String username) throws SQLException{
//通過姓名查詢用戶,通過ID同理
//獲得數(shù)據(jù)庫連接
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對象接受返回的數(shù)據(jù)
ResultSet rs=ptmt.executeQuery();
//通過users對象接受rs中的數(shù)據(jù)膝迎,如果rs中含有多個數(shù)據(jù)則使用數(shù)組接收
//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對象辉浦,調用函數(shù)即可