建立数据库连接:
对数据库进行连接操作:
DB.java文件
需要设置的参数:
1、启动数据库驱动:该驱动就是下载的数据库jar包中的Drive类的路径
2、连接路径:要连接的数据库的路径
jdbc:使用的数据库://localhost:数据库使用的端口号/连接的数据库名
例:jdbc:mysql://localhost:3306/Person
使用mysql数据库,数据库端口号为3306,连接的数据库名为Person
3、数据库用户名:设置的连接的用户名
4、数据库连接密码:设置的连接的密码
5、设置获取连接的对象
6、预编译对象
7、用于获取结果集的对象
以下参数写在类变量的定义区域
java">private static String driver = "com.mysql.cj.jdbc.Driver"; //数据库驱动类路径
private static String url = "jdbc:mysql://localhost:3306/Persn"; //连接的数据库路径
private static String user = "root"; //连接用户名
private static String pwd = "root"; //连接密码
private static Connection conn = null; //连接
启动对数据库的连接操作:
1、执行驱动
2、获取连接,填入数据库连接路径,用户名,密码
java">public static void main(String[] args) {
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,user,pwd);
System.out.println("数据库连接成功");
} catch (ClassNotFoundException e) {
System.out.println("找不到驱动!");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("数据库连接失败!请检查url,user,pwd");
e.printStackTrace();
}
}
异常:
Access denied for user ‘xxxx’@‘localhost’ (using password: YES):表示用户名或连接密码错误。
Unknown database ‘xxxx’:表示所连接的数据库不存在
增删改查
在进行以下操作时,我对DB.java该文件进行了部分改动:
java">import java.sql.*;
public class DB {
private static String driver = "com.mysql.cj.jdbc.Driver"; //数据库驱动类路径
private static String url = "jdbc:mysql://localhost:3306/Person"; //连接的数据库路径
private static String user = "root"; //连接用户名
private static String pwd = "root"; //连接密码
private static Connection conn = null; //连接
DB(){
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,user,pwd);
System.out.println("数据库连接成功");
} catch (ClassNotFoundException e) {
System.out.println("找不到驱动!");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("数据库连接失败!请检查url,user,pwd");
e.printStackTrace();
}
}
public static void setDriver(String driver) {
DB.driver = driver;
}
public static void setUrl(String url) {
DB.url = url;
}
public static void setUser(String user) {
DB.user = user;
}
public static void setPwd(String pwd) {
DB.pwd = pwd;
}
public static void setConn(Connection conn) {
DB.conn = conn;
}
public static String getDriver() {
return driver;
}
public static String getUrl() {
return url;
}
public static String getUser() {
return user;
}
public static String getPwd() {
return pwd;
}
public static Connection getConn() {
return conn;
}
}
将以上代码复制入Dao.java文件中即可使用。
新建一个类:Dao.java,存储对数据库的增删改查操作
属性:
java">private static String sql; //设置的预编译语句格式
private static PreparedStatement pstmt = null; //使用预编译语句
private static ResultSet rs = null; //获取的结果集
private static DB db = null; //连接数据库
Dao.java类构造函数:
java"> Dao(){
db = new DB(); //建立对数据库的连接
}
检查
检查结果集是否为空
java"> private Boolean checkRsNul(ResultSet rs){
try {
if (rs == null || !rs.next()){
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
增
java">public Boolean add(Person p){
sql = "SELECT * FROM DATA WHERE IDENTITY = ?"; //查询数据库中是否已经存在相同主键的数据
try {
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,p.getIdentity());
rs = pstmt.executeQuery();
if(!checkRsNul(rs)){ //若存在则不执行新增操作
System.out.println("新增数据的主键在数据库中已存在");
return false;
}
sql = "INSERT INTO DATA VALUES(?,?,?)"; //新增数据库的预编译格式
pstmt = db.getConn().prepareStatement(sql); //检查预编译格式
//将应写入的值替换占位符
pstmt.setString(1,p.getIdentity());
pstmt.setString(2,p.getName());
pstmt.setString(3,p.getSex());
//判断新增语句是否成功执行,成功执行该语句的返回值应该为1
if(pstmt.executeUpdate() == 0){
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
删
java">public Boolean del(String Identity){
sql = "SELECT * FROM DATA WHERE identity = ?";
try {
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,Identity);
rs = pstmt.executeQuery();
if (checkRsNul(rs)){ //检查结果集是否为空,若为空则不执行删除操作,返回false
return false;
}
sql = "DELETE FROM DATA WHERE identity = ?";
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,Identity);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
改
java">public Boolean update(String oldIdentity,String newIdentity){
sql = "SELECT * FROM DATA WHERE IDENTITY = ?";
try {
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,oldIdentity);
rs = pstmt.executeQuery();
if(checkRsNul(rs)){
System.out.println("指定修改数据不存在");
return false;
}
pstmt.setString(1,newIdentity);
rs = pstmt.executeQuery();
if(!checkRsNul(rs)){
System.out.println("需要修改的主键数据已存在");
return false;
}
sql = "UPDATE DATA SET IDENTITY= ? WHERE IDENTITY= ?";
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,newIdentity);
pstmt.setString(2,oldIdentity);
if (pstmt.executeUpdate() == 0){
System.out.println("更新失败!");
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
查
该方法用于从数据库中获得所需要的数据
java">public ArrayList<Person> getAll(){
sql = "SELECT * FROM DATA";
try {
pstmt = db.getConn().prepareStatement(sql);
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return getByRs(rs);
}
该方法用于将数据库中获取的结果取出
java"> private ArrayList<Person> getByRs(ResultSet rs){
ArrayList<Person> arrP = new ArrayList();
try {
if(checkRsNul(rs)){
return null;
}
do{
Person p = new Person();
p.setIdentity(rs.getString("identity"));
p.setName(rs.getString("name"));
p.setSex(rs.getString("sex"));
arrP.add(p);
}while (rs.next());
} catch (SQLException e) {
e.printStackTrace();
}
return arrP;
}
测试类
DBTest.java:对已写完的数据库操作类进行测试
增:
java">public static void main(String[] args) {
Dao d = new Dao();
Person p = new Person("555555555555555555","eee","男"); //增
if(d.add(p)){
System.out.println("新增数据操作成功!");
}else {
System.out.println("新增数据操作失败!");
}
}
删:
java">public static void main(String[] args) {
Dao d = new Dao();
if(d.del("555555555555555555")){
System.out.println("删除操作执行成功");
}else{
System.out.println("删除操作执行失败");
}
}
改:
java">public static void main(String[] args) {
Dao d = new Dao();
if(d.update("555555555555555555","666666666666666666")){
System.out.println("更新操作执行成功");
}else{
System.out.println("更新操作执行失败");
}
}
查:
java">public static void main(String[] args) {
Dao d = new Dao();
ArrayList<Person> arrP = d.getAll();
for (Person person : arrP) {
System.out.println(person);
}
}
异常
You have an error in your SQL syntax;check the manual that corresponds to your MySQL server version for the right syntax to use near…:Sql语句中存在错误,检查。
Duplicate entry ‘xxx’ for key ‘PRIMARY’:被修改的数据与数据库中的某个数据的主键冲突了。
Parameter index out of range (2 > number of parameters, which is 1):执行的预编译语句与传入的参数不相等。检查预编译操作是否有重新执行。
完整代码集合
Dao.java:
java">import com.sun.org.apache.regexp.internal.RE;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class Dao {
private static String sql; //设置的预编译语句格式
private static PreparedStatement pstmt = null; //使用预编译语句
private static ResultSet rs = null; //获取的结果集
private static DB db = null; //连接数据库
Dao(){
db = new DB();
}
public Boolean add(Person p){
sql = "SELECT * FROM DATA WHERE IDENTITY = ?";
try {
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,p.getIdentity());
rs = pstmt.executeQuery();
if(!checkRsNul(rs)){
System.out.println("新增数据的主键在数据库中已存在");
return false;
}
sql = "INSERT INTO DATA VALUES(?,?,?)"; //设置的预编译语句格式
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,p.getIdentity());
pstmt.setString(2,p.getName());
pstmt.setString(3,p.getSex());
if(pstmt.executeUpdate() == 0){
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
public Boolean del(String Identity){
sql = "SELECT * FROM DATA WHERE identity = ?";
try {
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,Identity);
rs = pstmt.executeQuery();
if (checkRsNul(rs)){
return false;
}
sql = "DELETE FROM DATA WHERE identity = ?";
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,Identity);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
public Boolean update(String oldIdentity,String newIdentity){
sql = "SELECT * FROM DATA WHERE IDENTITY = ?";
try {
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,oldIdentity);
rs = pstmt.executeQuery();
if(checkRsNul(rs)){
System.out.println("指定修改数据不存在");
return false;
}
pstmt.setString(1,newIdentity);
rs = pstmt.executeQuery();
if(!checkRsNul(rs)){
System.out.println("需要修改的主键数据已存在");
return false;
}
sql = "UPDATE DATA SET IDENTITY= ? WHERE IDENTITY= ?";
pstmt = db.getConn().prepareStatement(sql);
pstmt.setString(1,newIdentity);
pstmt.setString(2,oldIdentity);
if (pstmt.executeUpdate() == 0){
System.out.println("更新失败!");
return false;
}
} catch (SQLException e) {
e.printStackTrace();
}
return true;
}
public ArrayList<Person> getAll(){
sql = "SELECT * FROM DATA";
try {
pstmt = db.getConn().prepareStatement(sql);
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return getByRs(rs);
}
private ArrayList<Person> getByRs(ResultSet rs){
ArrayList<Person> arrP = new ArrayList();
try {
if(checkRsNul(rs)){
return null;
}
do{
Person p = new Person();
p.setIdentity(rs.getString("identity"));
p.setName(rs.getString("name"));
p.setSex(rs.getString("sex"));
arrP.add(p);
}while (rs.next());
} catch (SQLException e) {
e.printStackTrace();
}
return arrP;
}
private Boolean checkRsNul(ResultSet rs){
try {
if (rs == null || !rs.next()){
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
}
DB.java:
java">import java.sql.*;
public class DB {
private static String driver = "com.mysql.cj.jdbc.Driver"; //数据库驱动类路径
private static String url = "jdbc:mysql://localhost:3306/Person"; //连接的数据库路径
private static String user = "root"; //连接用户名
private static String pwd = "root"; //连接密码
private static Connection conn = null; //连接
DB(){
try {
Class.forName(driver);
conn = DriverManager.getConnection(url,user,pwd);
System.out.println("数据库连接成功");
} catch (ClassNotFoundException e) {
System.out.println("找不到驱动!");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("数据库连接失败!请检查url,user,pwd");
e.printStackTrace();
}
}
public static void setDriver(String driver) {
DB.driver = driver;
}
public static void setUrl(String url) {
DB.url = url;
}
public static void setUser(String user) {
DB.user = user;
}
public static void setPwd(String pwd) {
DB.pwd = pwd;
}
public static void setConn(Connection conn) {
DB.conn = conn;
}
public static String getDriver() {
return driver;
}
public static String getUrl() {
return url;
}
public static String getUser() {
return user;
}
public static String getPwd() {
return pwd;
}
public static Connection getConn() {
return conn;
}
}
DBTest:
java">import java.util.ArrayList;
public class DBTest {
public static void main(String[] args) {
Dao d = new Dao();
//增
/*
Person p = new Person("555555555555555555","eee","男"); //增
if(d.add(p)){
System.out.println("新增数据操作成功!");
}else {
System.out.println("新增数据操作失败!");
}
*/
//删
/*
if(d.del("555555555555555555")){
System.out.println("删除操作执行成功");
}else{
System.out.println("删除操作执行失败");
}
*/
//改
/*
if(d.update("555555555555555555","666666666666666666")){
System.out.println("更新操作执行成功");
}else{
System.out.println("更新操作执行失败");
}
*/
//查
/*
ArrayList<Person> arrP = d.getAll();
for (Person person : arrP) {
System.out.println(person);
}
*/
}
}
在进行测试时,要注意数据库中值的改变!
Person:
java">public class Person {
private String identity;
private String name;
private String sex;
public Person(String identity, String name, String sex) {
this.identity = identity;
this.name = name;
this.sex = sex;
}
public Person() {
}
public void setIdentity(String identity) {
this.identity = identity;
}
public void setName(String name) {
this.name = name;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getIdentity() {
return identity;
}
public String getName() {
return name;
}
public String getSex() {
return sex;
}
@Override
public String toString() {
return "Person{" +
"identity='" + identity + '\'' +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
'}';
}
}