初识JDBC
写在前面:本篇仅仅是对数据库的基本操作有一定的了解,仅仅是对JDBC有一定的使用和认识,望批评指点
注:本为以 MySql 为例
1.作用
在Java代码中嵌套sql代码对数据库进行操作,例如:增删改查
2.基本操作
- 上图为基本的对数据库的操作
- 增删改查的主要区别是sql的不同,细致来看可分为两种:
–增,改,删,可以分为一类对数据库数据进行改变
–查询主要是对数据库进行访问,并不修改数据
3.操作代码
我的示例有关信息:Person类为测试数据,进行程序间的信息的交互
- 代码类别分别放在四个文件夹中:
基础类 ,Dao及DaoImpl,domain, test - 具体代码和作用:
- Dao 接口定义规范并限制数据操作类:
package com.W.jdbc.dao;
import com.W.jdbc.domain.Person;
import java.util.List;
/**
* @Description : TODO{Dao抽象接口}
* @Creation Date : 2019/3/22 15:05
* @Time : 15:05
*/
public interface IPersonDao {
//保存
void Save(Person person);
//修改
void Updata(int id, Person person);
//删除
void Dele(int id);
//查询单个
Person Search(int id);
//查询所有
List<Person> SearchAll();
}
- domain中的基本类(本例中主要充当数据模板)
package com.W.jdbc.domain;
/**
* @Description : TODO{用于承担domain的任务}
* @Creation Date : 2019/3/22 13:35
* @Time : 13:35
*/
public class Person {
int id ;
String name = "";
int age ;
public void setAge(int age) {
this.age = age;
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public int getAge() {
return age;
}
@Override
public String toString() {
return "Person{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
- Dao实现类
package com.W.jdbc.dao.DaoImpl;
import com.W.jdbc.dao.IPersonDao;
import com.W.jdbc.domain.Person;
import com.W.jdbc.hader.BeanHander;
import com.W.jdbc.hader.IResulltSet;
import com.W.jdbc.util.CRUDTemplate;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Description : TODO{Dao实现类}
* @Creation Date : 2019/3/22 15:07
* @Time : 15:07
*/
public class IPersonDaoImpl implements IPersonDao {
@Override
public void Save(Person person) {
String sql = "insert into m_user(name, age) value(?, ?)";
CRUDTemplate.excuteUpdate(sql,person.getName(),person.getAge());
}
@Override
public void Updata(int id, Person person) {
String sql = "update m_user set name = ?, age = ? where id = ?";
CRUDTemplate.excuteUpdate(sql, person.getName(), person.getAge(),id);
}
@Override
public void Dele(int id) {
String sql = "delete from m_user where id = ?";
CRUDTemplate.excuteUpdate(sql,id);
}
@Override
public Person Search(int id) {
String sql = "select * from m_user where id = ?";
完整写法:
IResulltSet<List<Person>> iResulltSet = new PerResultSetHanderImp();
List<Person> personList = CRUDTemplate.excuteQuery(sql, iResulltSet, id);
// List<Person> personList = CRUDTemplate.excuteQuery(sql, new PerResultSetHanderImp(), id);
// return personList.size() > 0 ? personList.size() == 1 ? personList.get(0) : null : null;
//升级:
return CRUDTemplate.excuteQuery(sql, new BeanHander<Person>(Person.class), id);
}
@Override
public List<Person> SearchAll() {
String sql = "select * from m_user";
return CRUDTemplate.excuteQuery(sql, new PerResultSetHanderImp());
}
}
class PerResultSetHanderImp implements IResulltSet<List<Person>> {
@Override
public List<Person> handle(ResultSet resultSet) throws SQLException {
List<Person> list = new ArrayList<Person>();
while (resultSet.next()){
Person per = new Person();
per.setName(resultSet.getString("name"));
per.setAge(resultSet.getInt("age"));
per.setId(resultSet.getInt("id"));
list.add(per);
}
return list;
}
}
- test测试类
package com.W.jdbc.test;
import com.W.jdbc.dao.DaoImpl.IPersonDaoImpl;
import com.W.jdbc.domain.Person;
import org.junit.Test;
import java.util.List;
/**
* @Description : TODO{测试,是否成功插入}
* @Creation Date : 2019/3/22 15:28
* @Time : 15:28
*/
public class PersonDaoTest {
@Test
public void Save(){
Person person = new Person();
person.setAge(13);
person.setName("小刘");
IPersonDaoImpl per = new IPersonDaoImpl();
per.Save(person);
}
@Test
public void Dele(){
IPersonDaoImpl per = new IPersonDaoImpl();
per.Dele(40);
}
@Test
public void Updata(){
Person person = new Person();
person.setName("力123力");
person.setAge(19);
IPersonDaoImpl per = new IPersonDaoImpl();
per.Updata(38,person);
}
@Test
public void Search(){
IPersonDaoImpl per = new IPersonDaoImpl();
Person p = per.Search(5);
if (p == null) {
System.out.println("查询结果为空");
}else{
System.out.println(p.toString());
}
}
@Test
public void SearchAll() {
IPersonDaoImpl per = new IPersonDaoImpl();
List<Person> at = per.SearchAll();
for(Person p: at){
System.out.println(p.toString());
}
}
}
以上为对数据库操作的主要部分
其它涉及到的工具类代码:
- 工具1:CRUD(对基础操作的整和)
package com.W.jdbc.util;
import com.W.jdbc.domain.Person;
import com.W.jdbc.hader.IResulltSet;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Description : TODO{数据库操作公用数据——————提取为模板}
* @Creation Date : 2019/3/24 20:56
* @Time : 20:56
*/
public class CRUDTemplate {
public static int excuteUpdate(String sql, Object...params){
Connection con = null;
PreparedStatement ps = null;
try {
//获取连接
con = JdbcUtil.getConnection();
//执行SQL
ps = con.prepareStatement(sql);
//设置变量
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
//返回受影响的行
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.Close(con,ps,null);
}
return -1;
}
public static <T>T excuteQuery(String sql,IResulltSet<T> iResulltSet, Object...params){
Connection con = null;
PreparedStatement ps = null;
ResultSet set = null;
List<Person> list = new ArrayList<Person>();
try {
//连接数据库
con = JdbcUtil.getConnection();
//预处理
ps = con.prepareStatement(sql);
//遍历设置参数
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1, params[i]);
}
//执行SQl语句
set = ps.executeQuery();
//遍历结果集,并反对创建的对象
return iResulltSet.handle(set);
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.Close(con,ps,set);
}
return null;
}
}
- 工具2: JDBC(与数据库的连接的获得,及释放资源的操作)
package com.W.jdbc.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @Description : TODO{工具类}
* @Creation Date : 2019/3/22 19:28
* @Time : 19:28
*/
public class JdbcUtil {
public static DataSource ds = null;
static {
try {
//加载配置文件
Properties p = new Properties();
InputStream in = new FileInputStream("src/Source/db.properties");
p.load(in);
ds = DruidDataSourceFactory.createDataSource(p);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//连接
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//关闭
public static void Close(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 涉及配置文件:
- db.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/shopping?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
username=root
password=123456
4.对图片操作:
- 代码:
package com.W.jdbc.test;
import com.W.jdbc.util.JdbcUtil;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
/**
* @Description : TODO{操作图片入数据库}
* @Creation Date : 2019/3/23 20:12
* @Time : 20:12
*/
public class ImglTest {
public static void main(String[] args) throws IOException, SQLException {
ImglTest imglTest = new ImglTest();
// imglTest.putImag(new File("C:/Users/ANGLE0/Pictures/Saved Pictures/星空02.jpg"));
imglTest.getImag();
}
public void getImag() throws SQLException, IOException {
Connection con = null;
PreparedStatement ps = null;
con = JdbcUtil.getConnection();
String sql = "select * from b_user where id = ?";
ps = con.prepareStatement(sql);
ps.setInt(1, 2);
ResultSet set = ps.executeQuery();
if (set.next()) {
//获取图片
Blob blob = set.getBlob("imgl");
//获取图片二进制流
InputStream in = blob.getBinaryStream();
//把程序中的文件写入到磁盘中
Files.copy(in, Paths.get("d:/Picture.jpg"));
}
JdbcUtil.Close(con, ps,set);
}
public void putImag(File file){
Connection con = null;
PreparedStatement ps = null;
try {
con = JdbcUtil.getConnection();
String sql = "insert into b_user (imgl) value(?)";
ps = con.prepareStatement(sql);
ps.setBlob(1, new FileInputStream(file));
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
}finally {
JdbcUtil.Close(con, ps, null);
}
}
}
-
原图:
-
加载进数据库:
-
重新读取到磁盘:
4.事务处理:
代码:
package com.W.jdbc.test;
import com.W.jdbc.util.JdbcUtil;
import org.junit.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @Description : TODO{转账____事务处理}
* @Creation Date : 2019/3/23 17:23
* @Time : 17:23
*/
public class SendMoney {
@Test
public void sendMoney() throws SQLException {
this.sendMoney("zs","ls",1000);
}
public void sendMoney(String per_send, String per_get, int money) throws SQLException {
Connection con = null;
PreparedStatement ps = null;
ResultSet set = null;
try{
con = JdbcUtil.getConnection();
String sql = "select * from c_money where name = ? and money > ?";
//创建语句
ps = con.prepareStatement(sql);
ps.setString(1,per_send);
ps.setInt(2,money);
set = ps.executeQuery();
if (!set.next()){
throw new RuntimeException("余额不足!");
}
con.setAutoCommit(false);
//2.发送者减少账户部分资金
sql = "update c_money set money = money - ? where name = ?";
ps = con.prepareStatement(sql);
ps.setInt(1, money);
ps.setString(2, per_send);
ps.executeUpdate();
//3.接收者增加部分资金
sql = "update c_money set money = money + ? where name = ?";
ps = con.prepareStatement(sql);
ps.setInt(1, money);
ps.setString(2, per_get);
ps.executeUpdate();
con.commit();
}catch (Exception e){
e.printStackTrace();
con.rollback();
}finally {
//释放资源
JdbcUtil.Close(con, ps, set);
}
}
}
上述代码中 若在2和3之间程序异常终止,那么,数据已经修改显然这是不合理的,因此加上以下代码
con.setAutoCommit(false);
con.commit();
5.对配置文件的读取
代码:
package com.W.jdbc.test;
import org.junit.Test;
import javax.xml.bind.SchemaOutputResolver;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
/**
* @Description : TODO{读取配置文件}
* @Creation Date : 2019/3/24 17:23
* @Time : 17:23
*/
public class PropertiesTest {
@Test
public void getDbSource() throws IOException {
//创建Properties对象
Properties p = new Properties();
//读取文件
InputStream in = new FileInputStream("src/Source/db.properties");
//加载配置文件
p.load(in);
//投影Key的对应值
System.out.println("Driver = "+p.getProperty("driverClassName"));
System.out.println("username = "+p.getProperty("username"));
System.out.println("Password = "+p.getProperty("password"));
System.out.println("url = "+p.getProperty("url"));
}
}