JDBC的概念
JDBC是“Java Datebase Connecive”的缩写,表示使用Java去连接数据库进行数据操作的过程
使用Java连接数据库
public class TestMysql {
private static String URL =
"jdbc:mysql://localhost:3306/demo?useSSL=true&useUnicode=true&characterEncoding=UTF-8";
private static String DRIVER = "com.mysql.jdbc.Driver";
private static String USER = "root";
private static String PASSWORD = "123456";
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws SQLException {
// 取得连接对象
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
// 准备sql语句
String sql="INSERT INTO emp(empno,ename,job,sal,hiredate,mgr,comm,deptno)"+
"VALUES(1001,'李四','总裁',9000.00,NOW(),7788,2000.00,10)";
// 取得发送sql语句的对象
PreparedStatement pst = (PreparedStatement) conn.prepareCall(sql);
// 发送sql语句
int row = pst.executeUpdate();
System.out.println("插入了" + row + "行数据");
}
连接工具类
封装工具类
public class ConnectionUtil {
private static String URL =
"jdbc:mysql://localhost:3306/demo?useSSL=true&useUnicode=true&characterEncoding=UTF-8";
// 连接驱动的名称
private static String DRIVER = "com.mysql.jdbc.Driver";
// 用户名
private static String USER = "root";
// 密码
private static String PASSWORD = "123456";
// 加载驱动信息
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/*
* 取得连接对象
* */
public static Connection getConnection() {
try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
// 关闭连接
public static void close(Connection conn){
if(conn!=null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
插入数据
public class TestMysql {
public static void main(String[] args) throws Exception {
if (insertEmp()) {
System.out.println("插入数据成功");
} else {
System.out.println("插入数据失败");
}
}
// 插入一条数据
private static boolean insertEmp() throws SQLException {
// 取得连接对象
Connection conn = ConnectionUtil.getConnection();
// 定义sql语句
String sql= "INSERT INTO emp(empno,ename,job,sal,hiredate,mgr,comm,deptno)"
+ "VALUES(1002,'李四','总裁',9000.00,NOW(),7788,2000.00,10)";
// 取得发送sql语句的对象
java.sql.PreparedStatement pst = conn.prepareStatement(sql);
// 执行sql语句
int row = pst.executeUpdate();
ConnectionUtil.close(conn);
return row > 0;
}
更新数据
public class Update {
public static void main(String[] args) {
try {
updateDeposit(100.0);
} catch (SQLException e) {
e.printStackTrace();
}
}
// 更新数据与事务处理
// SMITH转账给ALLEN 100元
public static boolean updateDeposit(Double sum) throws SQLException {
Connection conn = ConnectionUtil.getConnection();
String sql1 = "UPDATE emp SET deposit=deposit-" + sum + "WHERE ename = 'SMITH'";
String sql2 = "UPDATE emp SET deposit=deposit+" + sum + "WHERE ename = 'ALLEN'";
// 取消事务自动提交
conn.setAutoCommit(false);
int row1=0;
int row2=0;
try {
PreparedStatement pst1=(PreparedStatement) conn.prepareStatement(sql1);
PreparedStatement pst2=(PreparedStatement) conn.prepareStatement(sql2);
row1=pst1.executeUpdate();
row2=pst1.executeUpdate();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
// 如果出现异常则事务回滚
conn.rollback();
}finally {
ConnectionUtil.close(conn);
}
return row1>0 && row2>0;
}
}
以上代码中第一个sql语句正常执行成功,但是第二个sql执行失败,此时我们先取消了事务的自动提交,只有所有的sql都执行成功再进行手工提交事务,否则就是事务回滚,保证了事务的原子性(要么全部成功,要么全部失败)。
删除数据
1.删除一条数据
public class Delete01 {
public static void main(String[] args) throws Exception {
System.out.println(deleteById(1001));
}
// 根据编号删除数据
public static boolean deleteById(Integer id) throws Exception {
Connection conn=ConnectionUtil.getConnection();
String sql="DELETE FROM emp WHERE empno=" + id;
PreparedStatement pst = conn.prepareStatement(sql);
int row=pst.executeUpdate();
ConnectionUtil.close(conn);
return row>0;
}
}
2.批量删除数据
public class Delete02 {
public static void main(String[] args) throws Exception {
Set<Integer> ids= new HashSet<>();
ids.add(1002);
ids.add(7788);
System.out.println(deleteBatch(ids));
}
public static boolean deleteBatch(Set<Integer> ids) throws Exception {
Connection conn = ConnectionUtil.getConnection();
StringBuffer sql=new StringBuffer("DELETE FROM emp WHERE empno IN(");
Iterator<Integer> iter = ids.iterator();
while (iter.hasNext()) {
sql.append(iter.next()+",");
}
// 删除最后一个逗号
sql.delete(sql.length()-1, sql.length());
// 添加一个右括号
sql.append(")");
PreparedStatement pst=conn.prepareStatement(sql+"");
int row = pst.executeUpdate();
ConnectionUtil.close(conn);
return row>0;
}
}
- 为什么以上sql语句要声明为StringBuffer 而不是String?
因为该字符串要频繁的修改,如果使用String会造成大量垃圾(Stringl类型一旦声明则内容不可以改变,改变的是引用,引用改变会导致垃圾产生)。所以使用StringBuffer.或者StringBuilder.
数据查询
方法一:
public class Query {
public static void main(String[] args) {
selectById(7369);
selectById1(7369);
}
public static void selectById(Integer id) {
Connection conn = ConnectionUtil.getConnection();
try {
String sql="SELECT empno,ename,job,hiredate,sal,mgr,deptno,comm FROM emp WHERE empno=" + id;
PreparedStatement pst;
pst = conn.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
if (rst.next()) {
System.out.println("雇员编号:"+rst.getInt(1)+",姓名:"+rst.getString(2)
+",职位:"+rst.getString(3)+",入职日期:"+rst.getDate(4)+",薪资:"+rst.getDouble(5)
+",佣金:"+rst.getDouble(6));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionUtil.close(conn);
}
}
方法二:
public static void selectById1(Integer id) {
Connection conn = ConnectionUtil.getConnection();
try {
String sql="SELECT empno,ename,job,hiredate,sal,mgr,deptno,comm FROM emp WHERE empno=" + id;
PreparedStatement pst = conn.prepareStatement(sql);
ResultSet rst = pst.executeQuery();
if (rst.next()) {
System.out.println("雇员编号:"+rst.getObject("empno")+",姓名:"+rst.getObject("ename")
+",职位:"+rst.getObject("job")+",入职日期:"+rst.getObject("hiredate")+",薪资:"
+rst.getObject("sal")+",佣金:"+rst.getObject("comm"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
ConnectionUtil.close(conn);
}
}