接上一篇：JDBC(一)基础.
用JDBC的API来做一些常规的练习。

1.连接工具类

  资源配置文件(src目录下)：jdbc.properties，里面定义了四行数据，分别对应着JDBC连接所需要的几个参数（注：Properties底层为一个Hashtable，配置文件中“=”之前的代表Map中的键，之后的代表相应键所对应的值）

url=jdbc:mysql://localhost:3306/db1?useSSL=false&characterEncoding=UTF-8
user=root
password=201703457
driver=com.mysql.jdbc.Driver

连接MySQL数据库的工具类:
JDBCUtils.java

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;/*** JDBC工具类*/
public class JDBCUtils {//声明用到参数对应的成员变量private static String url;private static String user;private static String password;private static String driver;/*** 配置文件的读取，只需要读取一次即可拿到这些值。使用静态代码块* 在程序编译的时候执行*/static {//读取资源文件，获取值。try {//1.创建Properties集合类。Properties pro = new Properties();//2.动态获取src路径下的文件的方式--->使用ClassLoader类:类加载器ClassLoader classLoader = JDBCUtils.class.getClassLoader();//获取ClassLoader对象URL res = classLoader.getResource("jdbc.properties");//定位jdbc.properties绝对路径资源String path = res.getPath();//获取资源的字符串路径//System.out.println(path);//打印一下资源路径//3.加载路径对应的文件pro.load(new FileReader(path));//4.获取数据，传值url = pro.getProperty("url");user = pro.getProperty("user");password = pro.getProperty("password");driver = pro.getProperty("driver");//5.注册驱动Class.forName(driver);} catch (IOException e) {e.printStackTrace();} catch (ClassNotFoundException e) {e.printStackTrace();}}/*** 获取连接* @return 连接对象*/public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}/*** 释放资源* @param stmt* @param conn*/public static void close(Statement stmt, Connection conn) {close(null,stmt,conn);}/*** 释放资源* @param stmt* @param conn* @param rs*/public static void close(ResultSet rs, Statement stmt, Connection conn) {if (rs != null) {//避免出现空指针异常,为了程序健壮try {rs.close();//释放资源} catch (SQLException e) {e.printStackTrace();}}if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();} catch (SQLException e) {e.printStackTrace();}}}}

2.基本操作

使用DDL语句

   在java中使用DDL语句(用的很少)，操作数据库，表，列等。关键字：create, drop,alter 等。

/*** JDBC操作* java中使用DDL语句(创建数据库、表...)*/
public class JDCBDemo_DDL {public static void main(String[] args) {Connection conn=null;PreparedStatement pstetm=null;try {//1.获取连接对象conn= JDBCUtils.getConnection();//2.定义sqlString sql="create database person";//创建数据库//String sql="create table student";//创建表//3.获取执行sql的对象pstetm=conn.prepareStatement(sql);//4.执行sql语句//判读是否成功if (pstetm.execute()){System.out.println("创建成功!");}else{System.out.println("创建失败！");}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(pstetm,conn);}}
}

CURD

对连接对象的数据库表中的数据增删改查。

/*** JDBC练习* CURD*/
public class JDBCDemo5 {/*** 插入操作*/@Testpublic void insert(){Connection conn=null;PreparedStatement pstatm=null;try {conn= JDBCUtils.getConnection();String sql="insert into user values(null,?,?)";pstatm=conn.prepareStatement(sql);pstatm.setString(1,"dong");pstatm.setString(2,"123456");int count=pstatm.executeUpdate();if (count>0){System.out.println("执行成功！");}else {System.out.println("插入失败！");}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(pstatm,conn);}}/*** 删除操作*/@Testpublic void delete(){Connection conn=null;PreparedStatement pstatm=null;try {conn=JDBCUtils.getConnection();String sql="delete from user where id=?";pstatm=conn.prepareStatement(sql);pstatm.setInt(1,3);int count=pstatm.executeUpdate();if (count>0){System.out.println("删除成功！");}else {System.out.println("失败！");}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(pstatm,conn);}}/*** 更新操作*/@Testpublic void update(){Connection conn=null;PreparedStatement pstatm=null;try {conn=JDBCUtils.getConnection();String sql="update user set NAME=?,password=? where id=?";pstatm=conn.prepareStatement(sql);pstatm.setString(1,"Liang");pstatm.setString(2,"888888");pstatm.setInt(3,4);int count=pstatm.executeUpdate();if (count>0){System.out.println("更新成功！");}else {System.out.println("失败！");}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(pstatm,conn);}}/*** 查询操作*/@Testpublic void query(){Connection conn=null;PreparedStatement pstatm=null;ResultSet rs=null;try {conn=JDBCUtils.getConnection();String sql="select * from user where id=?";pstatm=conn.prepareStatement(sql);pstatm.setInt(1,1);rs=pstatm.executeQuery();if (rs.next()){int id=rs.getInt(1);String name=rs.getString("NAME");String password=rs.getString(3);System.out.println(id+"-----"+name+"----"+password);}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(pstatm,conn);}}
}

3.常见操作

处理二进制和大文本数据

存取文本文件（.txt文件）

-- 数据库表
create table testclob(id int primary key auto_increment,txtfile text
);

Java程序

/*** 处理大数据* 存取文本文件(.txt文件)** 存：必须使用PreparedStatement接口中的方法。将指定输入流写入数据库文本字段* void setCharacterStream(int parameterIndex, InputStream x, int length)** 取：通过ResultSet接口的getCharacterStream(字段索引)方法读取字节流*/
public class JDBCDemo6 {/*** 将txt文件写入到数据表的字段中*/@Testpublic void add() {Connection conn = null;PreparedStatement pstmt = null;try {conn = JDBCUtils.getConnection();String sql = "insert into testclob(txtfile) values(?)";pstmt = conn.prepareStatement(sql);ClassLoader Cl=JDBCDemo6.class.getClassLoader();URL res=Cl.getResource("liang.txt");String path=res.getPath();//String path = JDBCDemo6.class.getClassLoader().getResource("liang.txt").getPath();//System.out.println(path);File file = new File(path);pstmt.setCharacterStream(1, new FileReader(file), (int) file.length());int count = pstmt.executeUpdate();if (count > 0) {System.out.println("存入成功！");} else {System.out.println("失败！");}} catch (SQLException e) {e.printStackTrace();} catch (FileNotFoundException e) {e.printStackTrace();} finally {JDBCUtils.close(pstmt, conn);}}/*** 读取数据库中的文件*/@Testpublic void read(){Connection conn = null;PreparedStatement pstmt = null;ResultSet rs=null;try {conn=JDBCUtils.getConnection();String sql="select txtfile from testclob where id =?";pstmt=conn.prepareStatement(sql);pstmt.setInt(1,1);rs=pstmt.executeQuery();if (rs.next()){System.out.println("读取到的文件内容：");Reader reader=rs.getCharacterStream("txtfile");BufferedReader bufferedReader=new BufferedReader(reader);String s=null;while ((s=bufferedReader.readLine())!=null){System.out.println(s);}reader.close();}} catch (SQLException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}finally {JDBCUtils.close(rs,pstmt,conn);}}
}

存取二进制文件（图片，视频）

-- 数据库表
create table testblob(id int primary key auto_increment,image LONGBLOB
);

Java程序

/*** 处理大数据* 存取二进制文件(图片,视频)**存：必须使用PreparedStatement接口中的方法。将二进制输入流数据写入二进制字段中* void setBinaryStream(int parameterIndex, InputStream x, int length)** 取：通过ResultSet接口的getBinaryStream(二进制字段索引)方法读取字节流*/
public class JDBCDemo7 {/*** 将图片写入到数据表的字段中*/@Testpublic void add(){Connection conn=null;PreparedStatement pstetm=null;try {conn= JDBCUtils.getConnection();String sql="insert into testblob(image) values(?)";pstetm=conn.prepareStatement(sql);String path=JDBCDemo7.class.getClassLoader().getResource("1.png").getPath();pstetm.setBinaryStream(1,new FileInputStream(path),(int)new File(path).length());int count=pstetm.executeUpdate();if (count>0){System.out.println("添加成功！");}else {System.out.println("失败！");}} catch (SQLException e) {e.printStackTrace();} catch (FileNotFoundException e) {e.printStackTrace();}finally {JDBCUtils.close(pstetm,conn);}}/*** 从数据表中读取图片*/@Testpublic void read(){Connection coon=null;PreparedStatement pstatm=null;ResultSet rs=null;try {coon=JDBCUtils.getConnection();String sql="select image from testblob where id=?";pstatm=coon.prepareStatement(sql);pstatm.setInt(1,1);rs=pstatm.executeQuery();if (rs.next()){InputStream in=rs.getBinaryStream("image");int len=0;byte []bytes=new byte[1024];FileOutputStream out=new FileOutputStream("E:\\1.png");while ((len=in.read(bytes))!=-1){out.write(bytes,0,len);}in.close();out.close();System.out.println("读取成功！");}} catch (Exception e) {e.printStackTrace();}finally {JDBCUtils.close(rs,pstatm,coon);}}
}

批量处理SQL

-- 数据库表
create table testBatch(id int primary key auto_increment,name VARCHAR(20)
);

Java程序

/*** 批量处理sql语句*/
public class JDBCDemo8 {/*** 通过Statement对象实现*/@Testpublic void testBatch1(){Connection conn=null;Statement statm=null;try {conn= JDBCUtils.getConnection();String sql1="insert into testBatch(id,name) values(1,'dong')";String sql2="insert into testBatch(id,name) values(2,'liang')";String sql3="update testBatch set name='aaa' where id=1";statm=conn.createStatement();statm.addBatch(sql1);statm.addBatch(sql2);statm.addBatch(sql3);statm.executeBatch();statm.clearBatch();System.out.println("批量处理成功！");} catch (SQLException e) {e.printStackTrace();System.out.println("失败！");}finally {JDBCUtils.close(statm,conn);}}/*** 通过PreparedStatement对象实现*/@Testpublic void testBatch2(){long starTime=System.currentTimeMillis();Connection conn=null;PreparedStatement pstatm=null;try {conn= JDBCUtils.getConnection();String sql="insert into testBatch(name) values(?)";pstatm=conn.prepareStatement(sql);for (int i = 0; i <500; i++) {pstatm.setString(1,"D"+i);pstatm.addBatch();}pstatm.executeBatch();System.out.println("批量处理成功！");} catch (SQLException e) {e.printStackTrace();System.out.println("失败！");}finally {JDBCUtils.close(pstatm,conn);}long endTime=System.currentTimeMillis();System.out.println("程序运行时间："+(endTime-starTime)/1000+"秒！");}
}

获取自动增长主键列

-- 数据库表
create table test(id int primary key auto_increment,name VARCHAR(20)
);

Java程序

/*** 获取自动生成的主键* 通过PreparedStatement对象getGeneratedKeys方法得到主键列*/
public class JDBCDemo9 {public static void main(String[] args) {Connection conn=null;PreparedStatement pstatm=null;ResultSet rs=null;try {conn= JDBCUtils.getConnection();String sql="insert into test(name) values(?)";//在插入name时,自动生成idpstatm=conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);//需要给定参数Statement.RETURN_GENERATED_KEYSpstatm.setString(1,"dong");pstatm.executeUpdate();rs=pstatm.getGeneratedKeys();//获取自动增长的主键列if (rs.next()){System.out.println(rs.getInt(1));}System.out.println("执行成功！");} catch (SQLException e) {e.printStackTrace();System.out.println("失败！");}finally {JDBCUtils.close(rs,pstatm,conn);}}
}

数据库元数据

  什么是元数据？描述数据库或其组成部分的数据成为元数据（区别于那些存在数据库中的实际数据）。
通过JDBC可以获得三种元数据，数据库元数据，SQL语句参数元数据，结果集元数据。

-- 数据库表
CREATE TABLE `emp` (`id` int(11) NOT NULL,`name` varchar(255) default NULL,`address` varchar(255) default NULL,`hireDate` datetime default NULL,PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE emp MODIFY id INT(11) auto_increment;
INSERT INTO emp(name,address) VALUES('张三','武汉');
INSERT INTO emp(name,address) VALUES('李四','武汉');
INSERT INTO emp(name,address) VALUES('王五','长沙');
INSERT INTO emp(name,address) VALUES('赵云','北京');
INSERT INTO emp(name,address) VALUES('刘备','深圳');
INSERT INTO emp(name,address) VALUES('曹操','上海');

Java程序

/*** JDBC练习* 元数据：其实就是数据库，表，列的定义信息。* 利用元数据可以对我们的工具类再进行抽象和封装，形成更好的工具类*/
public class JDBCDemo11 {/*** 获取数据库元数据*/@Testpublic void getDatabaseMetData(){Connection conn=null;DatabaseMetaData metaData=null;try {conn= JDBCUtils.getConnection();metaData=conn.getMetaData();//获取数据库名称String DBname=metaData.getDatabaseProductName();System.out.println(DBname);//获取驱动版本String driverNmae=metaData.getDriverName();System.out.println(driverNmae);//获取数据库连接URLString url=metaData.getURL();System.out.println(url);//获取用户名String userName=metaData.getUserName();System.out.println(userName);} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(null,conn);}}/*** 获取SQL执行对象元数据*/@Testpublic void getParameterMetaData(){Connection conn=null;PreparedStatement pstatm=null;ParameterMetaData metaData=null;try {conn=JDBCUtils.getConnection();String sql="insert into emp(id,name,address,hiredate) values(?,?,?,?)";pstatm=conn.prepareStatement(sql);metaData=pstatm.getParameterMetaData();//取到的参数个数int Count=metaData.getParameterCount();System.out.println(Count);} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(pstatm,conn);}}/*** 获取结果集元数据*/@Testpublic void getResultSetMetaData(){Connection conn=null;PreparedStatement pstatm=null;ResultSet rs=null;try {conn=JDBCUtils.getConnection();String sql="select * from emp";pstatm=conn.prepareStatement(sql);rs=pstatm.executeQuery();// 获取结果集元数据ResultSetMetaData resultSetMetaData = rs.getMetaData();// 获取查询出来的总列数int columnCount = resultSetMetaData.getColumnCount();System.out.println("columnCount : " + columnCount);// 获取指定列的类型常量，在java.sql.Types类中可以查找到int columnType = resultSetMetaData.getColumnType(2);System.out.println("columnType : " + columnType);// 获取指定列的类型字符串，如INT,VARCHARString columnTypeName = resultSetMetaData.getColumnTypeName(2);System.out.println("columnTypeName : " + columnTypeName);// 获取指定列的列名，如hiredateString columnName = resultSetMetaData.getColumnName(4);System.out.println("columnName : " + columnName);} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(rs,pstatm,conn);}}
}

封装查询结果集

数据库表以上面的emp表为例子。

/*** 新建实体类Emp*/
public class Emp {private int id;private String name;private String address;private Date hireDate;public Emp(int id, String name, String address, Date hireDate) {this.id = id;this.name = name;this.address = address;this.hireDate = hireDate;}public Emp() {}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public Date getHireDate() {return hireDate;}public void setHireDate(Date hireDate) {this.hireDate = hireDate;}@Overridepublic String toString() {return "Emp{" +"id=" + id +", name='" + name + '\'' +", address='" + address + '\'' +", hireDate=" + hireDate +'}';}
}

/*** JDBC练习* 封装查询结果集* 定义一个方法，查询Emp表的数据将其封装为对象，然后装载集合，返回。*/
public class JDBCDemo12 {public static void main(String[] args) {List<Emp> list=new JDBCDemo12().finAll();//打印list集合System.out.println(list);}/*** 查询所有emp对象* @return*/public List<Emp> finAll(){Connection conn=null;PreparedStatement pstate=null;ResultSet rs=null;List<Emp> list=null;try {conn= JDBCUtils.getConnection();String sql="select * from emp";pstate=conn.prepareStatement(sql);rs=pstate.executeQuery();//遍历结果集，封装对象，装载集合Emp emp=null;list=new ArrayList<Emp>();while (rs.next()){//获取数据int id=rs.getInt("id");String name=rs.getString("name");String address=rs.getString("address");Date hireDate=rs.getDate("hireDate");//创建emp对象,并赋值emp=new Emp();emp.setId(id);emp.setName(name);emp.setAddress(address);emp.setHireDate(hireDate);//装载集合list.add(emp);}} catch (SQLException e) {e.printStackTrace();}finally {JDBCUtils.close(rs,pstate,conn);}return list;}
}