例1、jdbc连接数据库的实例一。
第一步、在一个java project里面新建一个Folder命名为lib,在里面放mysql-connector-java-5.1.10-bin.jar,并设置为build path,
第二步、TestConnection.java
package cn.itcast.mysql.connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Driver;
public class TestConnection {
public static void main(String[] args) throws Exception{
//真正开发中的连接
String url="jdbc:mysql://localhost:3306/test";
//1、注册驱动,Class.forName(String classpath):加载字节码文件,会调用类中的静态代码块,但不会调用类的构造方法
//调用com.mysql.jdbc.Driver 类中的静态代码块,注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2、获取连接
Connection conn = (Connection) DriverManager.getConnection(url, "root", "root");
System.out.println(conn);
}
/*
* 采用注册驱动的方式连接
*/
public static void registerDriver() throws Exception{
//采用注册驱动的方法
String url="jdbc:mysql://localhost:3306/test";
//1、注册驱动
DriverManager.registerDriver(new Driver());
//2、获取连接
Connection conn = (Connection) DriverManager.getConnection(url, "root", "root");
System.out.println(conn);
}
/*
* 通过driver接口连接
*/
public static void testDriver(){
try {
Driver driver=new Driver();
Properties info=new Properties();
info.setProperty("user", "root");
info.setProperty("password", "root");
String url="jdbc:mysql://localhost:3306/test";
Connection conn=(Connection) driver.connect(url, info);
System.out.println("conn"+conn);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
例2、利用JDBC对数据库进行增删改查.(实际开发中异常不能抛出)
package cn.itcast.mysql.statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.Statement;
public class TestStatement {
public static void main(String[] args) throws Exception{
Connection conn=null;
Statement statement=null;
//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");
/*2、获取连接 conn = DriverManager.getConnection(url, "root","root");
* *第一个参数:连接的URL地址
* *第二个参数:user(用户名)
* *第三个参数:password(密码)
* String url="jdbc:mysql://localhost:3306/test";
* *jdbc:表示连接数据库使用的jdbc协议
* *mysql:mysql自协议,该协议根据数据库的不同而变化,mysql表示连接的是mysql数据库
* *localhost:3306/test 定义到某个数据库
*/
String url="jdbc:mysql://localhost:3306/test";
conn = DriverManager.getConnection(url, "root","root");
System.out.println("conn"+conn);
/*3、创建statement对象
* conn.createStatement();创建一个对象将SQL语句发送到数据库中
*/
statement=(Statement) conn.createStatement();
/*4、组织、执行SQL语句
* *(1)执行给定SQL语句,该语句可能为INSERT UPDATE DELETE
* 对于 SQL 数据操作语言 (DML) 语句,返回受影响的行数
* *(2)执行DDL语句(create drop alter)
* 对于不返回任何内容的 SQL 语句(DDL语句),返回 0
*/
//String sqlInsert="INSERT INTO test1(id,NAME,sex) VALUES(NULL,'张','男')";
//String sqlUpdate="UPDATE test1 SET NAME='樊',sex='女' WHERE id=1";
//String sqlDelete="DELETE FROM test1 WHERE id=1";
String sqlCreate="CREATE TABLE test2(id INT,NAME VARCHAR(20))";
//int k = statement.executeUpdate(sqlInsert);
//int y=statement.executeUpdate(sqlUpdate);
//int x=statement.executeUpdate(sqlDelete);
int a=statement.executeUpdate(sqlCreate);
//System.out.println("k"+ k);
//System.out.println("y"+ y);
//System.out.println("x"+x);
System.out.println("a"+a);
//5、关闭资源
if(statement!=null){
statement.close();
}
if(conn!=null){
conn.close();
}
}
}
例3、利用jdbc实现对数据库的查询select ResultSet
第一步、表employees:
第二步、
package cn.itcast.mysql.statement;
import java.sql.*;
public class TestStatementResultSet {
//查询员工表的信息
public static void main(String[] args){
Connection conn=null;
Statement statement=null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url="jdbc:mysql://localhost:3306/test";
try {
conn=DriverManager.getConnection(url, "root","root");
statement=conn.createStatement();
String sql="SELECT employee_id,first_name,last_name,job,salary,comm,"+
"hiredate,department_id FROM test.employees";
rs= statement.executeQuery(sql);
/* statement.executeQuery(sql);这里的sql通常为静态的select语句
* 方法的返回值:包含给定查询所生成数据的ResultSet对象,永远不能为null
* ResultSet:表示数据库结果集的数据表,通常通过执行查询数据库的语句生成。
* *获取结果集中数据的方法:
* *方法一:getXxx("字段名称") Xxx表述在数据库中的数据类型在java中对应过的类型
* *例如 int getInt()
* *当使用别名时,getXxx("字段名称")中的字段名称应该为列的别名
* *方法二:getXxx(int columnIndex);int columnIndex列的索引值
* *例如: employee_id,first_name,last_name ……
* 1 2 3 ……
* *方法三:getObject(); 所有列信息都可以通过object获取
* *例如:
* System.out.print("employee_id"+":"+rs.getObject("employee_id"));
* System.out.print("employee_id"+":"+rs.getObject(1));
*/
①
rs.next();//将指针向前移动一步
System.out.println("employee_id"+rs.getInt("employee_id"));
System.out.println("first_name"+rs.getString("first_name"));
System.out.println("job"+rs.getString("job"));
System.out.println("salary" + rs.getDouble("salary"));
System.out.println("hiredate" + rs.getDate("hiredate"));
//关闭资源
if(rs!=null){
rs.close();
}
if(statement!=null){
statement.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
注意,当程序运行到①处是,rs的内容为一下内容,指针(光标)的位置如箭头所示:
当执行了语句rs.next();后,指针向下移动一个位置:
所以运行结果为:
employee_id:2
first_name:西门A
job:经理
salary:4500.0
hiredate:null
查询所有信息:用while循环
方法一:
while(rs.next()){
System.out.print("employee_id"+":"+rs.getInt("employee_id"));
System.out.print("first_name"+":"+rs.getString("first_name"));
System.out.print("job"+":"+rs.getString("job"));
System.out.print("salary" + ":"+rs.getDouble("salary"));
System.out.print("hiredate" + ":"+rs.getDate("hiredate"));
System.out.println();}
方法二:
while(rs.next()){
System.out.print("employee_id"+":"+rs.getInt(1));
System.out.print("first_name"+":"+rs.getString(2));
System.out.print("job"+":"+rs.getString(4));
System.out.print("salary" + ":"+rs.getDouble(5));
System.out.print("hiredate" + ":"+rs.getDate(7));
System.out.println();
}
方法三:
while(rs.next()){
System.out.print("employee_id"+":"+rs.getObject(1));
System.out.print("first_name"+":"+rs.getObject (2));
System.out.print("job"+":"+rs. getObject(4));
System.out.print("salary" + ":"+rs.getObject(5));
System.out.print("hiredate" + ":"+rs.getObject(7));
System.out.println();
}
结果为:
employee_id:2first_name:西门Ajob:经理salary:4500.0hiredate:null
employee_id:3first_name:AAjob:员工salary:4000.0hiredate:null
employee_id:4first_name:西门job:员工salary:4200.0hiredate:null
employee_id:5first_name:樊job:企划部经理salary:4000.0hiredate:2007-09-09
employee_id:1first_name:邓job:nullsalary:8900.0hiredate:null
例2、jdbc工具类的书写及其测试。
第一步、
package cn.itcast.mysql.util;
import java.sql.*;
//数据库管理类
public class DBManager {
private String className="com.mysql.jdbc.Driver";
private String username="root";
private String password="root";
private String url="jdbc:mysql://localhost:3306/test";
//1.注册驱动
public DBManager(){
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//2.获取连接
public Connection getConnection(){
Connection conn=null;
try {
conn=DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//3.关闭资源
public void closeResource(Connection conn,Statement statement,ResultSet rs){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(statement!=null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
第二步、
package cn.itcast.mysql.util;
import java.sql.*;
public class Test {
public static void main(String[] args) {
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
//1.注册驱动
dbManager=new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.创建statement对象
try {
statement = conn.createStatement();
String sqlInsert="INSERT INTO test1(id,NAME,sex) VALUES(NULL,'张','男')";
statement.executeUpdate(sqlInsert);
} catch (SQLException e) {
e.printStackTrace();
}finally{
//4.关闭资源
if(dbManager!=null){
dbManager.closeResource(conn, statement, null);
}
}
}
}
例3、JDBC练习题之对表进行增删改查。
第O步、建表
第一步、创建package cn.itcast.mysql.util 放入工具类
第二步、创建package cn.itcast.mysql.college
package cn.itcast.mysql.college;
//存储表的数据
public class College {
/*
CREATE TABLE college
(
id INT AUTO_INCREMENT PRIMARY KEY,
sex CHAR(2),
idCard VARCHAR(18),
acard VARCHAR(15),
NAME VARCHAR(12),
AREA VARCHAR(30),
grate DOUBLE(5,2)
)
*/
private Integer id;
private String sex;
private String idCard;
private String acard;
private String name;
private String area;
private Double grate;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getIdCard() {
return idCard;
}
public void setIdCard(String idCard) {
this.idCard = idCard;
}
public String getAcard() {
return acard;
}
public void setAcard(String acard) {
this.acard = acard;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getArea() {
return area;
}
public void setArea(String area) {
this.area = area;
}
public Double getGrate() {
return grate;
}
public void setGrate(Double grate) {
this.grate = grate;
}
}
第三步、
package cn.itcast.mysql.college;
public class DaoCollegeImpl {
//保存数据
public void saveCollege(College c){
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
//1.注册驱动
dbManager = new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.组织SQL语句
String sql="INSERT INTO college(id,sex,idCard,acard,NAME,AREA,grate) VALUES(null," + "'"+c.getSex()+"','"+c.getIdCard()+"','"+c.getAcard()+"','"+c.getName()+"'," +
"'"+c.getArea()+"','"+c.getGrate()+"')";//注意:换行的行末必须有符号
System.out.println("sql"+sql);
//4.创建statement对象
try {
statement=conn.createStatement();
//5.执行SQL语句
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
dbManager.closeResource(conn, statement, null);
}
}
//通过id更新表中数据
public void updateCollege(College c) {
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
//1.注册驱动
dbManager = new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.组织SQL语句
String sql="update college set sex='"+c.getSex()+"',idCard='"+c.getIdCard()+"'," +"acard='"+c.getAcard()+"',name='"+c.getName()+"',area='"+c.getArea()+"'," +
"grate="+c.getGrate()+" where id="+c.getId()+"";
//注意上面的断字符串,String类型的才要单引号,其他类型不要单引号
System.out.println("sql"+sql);
//4.创建statement对象
try {
statement=conn.createStatement();
//5.执行SQL语句
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
dbManager.closeResource(conn, statement, null);
}
}
public void deleteCollegeById(int id) {
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
//1.注册驱动
dbManager = new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.组织SQL语句
String sql="delete from college where id="+id;
//注意上面的断字符串,String类型的才要单引号,其他类型不要单引号
System.out.println("sql"+sql);
//4.创建statement对象
try {
statement=conn.createStatement();
//5.执行SQL语句
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}finally{
//6.关闭连接
dbManager.closeResource(conn, statement, null);
}
}
//查询所有
public List<College> findAllCollege() {
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
List<College> list=null;
//1.注册驱动
dbManager = new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.组织SQL语句
String sql="select id,sex,idCard,acard,name,area,grate from college order by id";
System.out.println("sql"+sql);
//4.创建statement对象
try {
statement=conn.createStatement();
//5.执行SQL语句
rs = statement.executeQuery(sql);
list=new ArrayList<College>();
//6.遍历
while(rs.next()){
//7.封装数据到javabean -->list
College c=new College();
c.setId(rs.getInt("id"));
c.setAcard(rs.getString("acard"));
c.setArea(rs.getString("area"));
c.setGrate(rs.getDouble("grate"));
c.setName(rs.getString("name"));
c.setSex(rs.getString("sex"));
c.setIdCard(rs.getString("idCard"));
list.add(c);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
//8.关闭连接
dbManager.closeResource(conn, statement, rs);
}
return list;
}
public College findCollege(int id) {
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
College c=null;
List<College> list=null;
//1.注册驱动
dbManager = new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.组织SQL语句
String sql="select id,sex,idCard,acard,name,area,grate from college where id="+id;
//4.创建statement对象
try {
statement=conn.createStatement();
//5.执行SQL语句
rs = statement.executeQuery(sql);
//6.遍历
while(rs.next()){
//7.封装数据到javabean
c=new College();
c.setId(rs.getInt("id"));
c.setAcard(rs.getString("acard"));
c.setArea(rs.getString("area"));
c.setGrate(rs.getDouble("grate"));
c.setName(rs.getString("name"));
c.setSex(rs.getString("sex"));
c.setIdCard(rs.getString("idCard"));
return c;
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
//8.关闭连接
dbManager.closeResource(conn, statement, null);
}
return c;
}
}
第四步、
package cn.itcast.mysql.college;
import java.util.List;
public class Client {
public static void main(String[] args) {
/****************************************************************
//1.添加数据到表中,javaweb中从页面取到 request.getParamete("");
College c=new College();
c.setSex("男");
c.setIdCard("1111111111111");
c.setAcard("222222222");
c.setName("张三");
c.setArea("广东");
c.setGrate(120.00);
DaoCollegeImpl daoCollege=new DaoCollegeImpl();
daoCollege.saveCollege(c);//在表中插入数据行
******************************************************************
//2.更新
College c=new College();
c.setId(2);
c.setSex("女");
c.setIdCard("33333333333333333");
c.setAcard("4444444444444");
c.setName("李四");
c.setArea("四川");
c.setGrate(125.00);
DaoCollegeImpl daoCollege=new DaoCollegeImpl();
daoCollege.updateCollege(c);
******************************************************************
//删除
DaoCollegeImpl daoCollege=new DaoCollegeImpl();
daoCollege.deleteCollegeById(3);
******************************************************************
//查询所有
DaoCollegeImpl daoCollege=new DaoCollegeImpl();
List<College> list = daoCollege.findAllCollege();
for(College c:list){
System.out.println(c.getId()+" "+c.getName());
}
******************************************************************/
//通过id查询
DaoCollegeImpl daoCollege=new DaoCollegeImpl();
College c = daoCollege.findCollege(1);
if(c!=null){
System.out.println(c.getId()+" "+c.getName());
}
}
}
例4、PreparedStatement对象对数据库增删改.
package cn.itcast.mysql.preparedStatement;
import java.sql.*;
import cn.itcast.mysql.util.DBManager;
public class TestPreparedStatement {
public static void main(String[] args) throws Exception {
preparedStatementDelete();
}
public static void preparedStatementUpdate(){
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
//1.注册驱动
dbManager=new DBManager();
//2.获取连接
conn=dbManager.getConnection();
/*
* 3.组织SQL语句
* *使用?占位
* *?的顺序有对应关系
* *?的索引从一开始,依次是1 2 3 4 5....
* update college sex=?,idCard=?,acard=?,NAME=?,AREA=?,grate=? where id=?
* ?索引值 1 2 3 4 5 6 7
*/
String sql="update college set sex=?,idCard=?,acard=?,NAME=?,AREA=?,grate=? where id=?";
//4.创建PreparedStatement对象(表示预编译的SQL语句对象)
try {
pstatement = conn.prepareStatement(sql);
//5.设置参数值 pstatement.setXxx(?的索引值,值)
pstatement.setString(1, "男");
pstatement.setString(2, "1212121212");
pstatement.setString(3, "34343434");
pstatement.setString(4, "樊冬");
pstatement.setString(5, "四川");
pstatement.setDouble(6, 78.56);
pstatement.setInt(7, 2);
/*6、组织、执行SQL语句
* *(1)执行给定SQL语句,该语句可能为INSERT UPDATE DELETE
* 对于 SQL 数据操作语言 (DML) 语句,返回受影响的行数
* *(2)执行DDL语句(create drop alter)
* 对于不返回任何内容的 SQL 语句(DDL语句),返回 0
*/
int k = pstatement.executeUpdate();
System.out.println("k"+":"+k);
} catch (SQLException e) {
e.printStackTrace();
}
//7.关闭连接
dbManager.closeResource(conn, pstatement, null);
}
public static void preparedStatementInsert(){
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
//1.注册驱动
dbManager=new DBManager();
//2.获取连接
conn=dbManager.getConnection();
/*
* 3.组织SQL语句
* *使用?占位
* *?的顺序有对应关系
* *?的索引从一开始,依次是1 2 3 4 5....
* values(null, ?, ?, ?, ?, ?, ?)
* id, sex,idCard,acard,NAME,AREA,grate
* ?索引值 1 2 3 4 5 6
*/
String sql="INSERT INTO college(id,sex,idCard,acard,NAME,AREA,grate) values(null,?,?,?,?,?,?)";
//4.创建PreparedStatement对象(表示预编译的SQL语句对象)
try {
pstatement = conn.prepareStatement(sql);
//5.设置参数值 pstatement.setXxx(?的索引值,值)
pstatement.setString(1, "男");
pstatement.setString(2, "1212121212");
pstatement.setString(3, "34343434");
pstatement.setString(4, "刘备");
pstatement.setString(5, "四川");
pstatement.setDouble(6, 78.56);
/*6、组织、执行SQL语句
* *(1)执行给定SQL语句,该语句可能为INSERT UPDATE DELETE
* 对于 SQL 数据操作语言 (DML) 语句,返回受影响的行数
* *(2)执行DDL语句(create drop alter)
* 对于不返回任何内容的 SQL 语句(DDL语句),返回 0
*/
int k = pstatement.executeUpdate();
System.out.println("k"+":"+k);
} catch (SQLException e) {
e.printStackTrace();
}
//7.关闭连接
dbManager.closeResource(conn, pstatement, null);
}
public static void preparedStatementDelete(){
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
//1.注册驱动
dbManager=new DBManager();
//2.获取连接
conn=dbManager.getConnection();
/*
* 3.组织SQL语句
* *使用?占位
* *?的顺序有对应关系
* *?的索引从一开始,依次是1 2 3 4 5....
*/
String sql="delete from college where id=?";
//4.创建PreparedStatement对象(表示预编译的SQL语句对象)
try {
pstatement = conn.prepareStatement(sql);
//5.设置参数值 pstatement.setXxx(?的索引值,值)
pstatement.setInt(1,5);
/*6、组织、执行SQL语句
* *(1)执行给定SQL语句,该语句可能为INSERT UPDATE DELETE
* 对于 SQL 数据操作语言 (DML) 语句,返回受影响的行数
* *(2)执行DDL语句(create drop alter)
* 对于不返回任何内容的 SQL 语句(DDL语句),返回 0
*/
int k = pstatement.executeUpdate();
System.out.println("k"+":"+k);
} catch (SQLException e) {
e.printStackTrace();
}
//7.关闭连接
dbManager.closeResource(conn, pstatement, null);
}
}
例5、PreparedStatement对象对数据库进行查询。
package cn.itcast.mysql.preparedStatement;
import java.sql.*;
import cn.itcast.mysql.util.DBManager;
public class TestPreparedStatementResult {
public static void main(String[] args) {
findObjectById();
}
public static void findObjectById(){
DBManager dbManager=null;
Connection conn=null;
ResultSet rs=null;
PreparedStatement pstatement=null;
//1.注册驱动
dbManager=new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.组织SQL语句
String sql="select id,sex,idCard,acard,name,area,grate from college where id=?";
//4.创建PreparedStatement对象(表示预编译的SQL语句对象)
try {
pstatement = conn.prepareStatement(sql);
//5.设置参数值
pstatement.setInt(1, 2);//查询id=2的信息
rs=pstatement.executeQuery();
while(rs.next()){
System.out.print(rs.getInt(1));
System.out.print(rs.getString(2));
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
//6.关闭连接
dbManager.closeResource(conn, pstatement, rs);
}
public static void findAll(){
DBManager dbManager=null;
Connection conn=null;
ResultSet rs=null;
PreparedStatement pstatement=null;
//1.注册驱动
dbManager=new DBManager();
//2.获取连接
conn=dbManager.getConnection();
//3.组织SQL语句
String sql="select id,sex,idCard,acard,name,area,grate from college order by id";
//4.创建PreparedStatement对象(表示预编译的SQL语句对象)
try {
pstatement = conn.prepareStatement(sql);
//5.执行select语句,并返回ResultSet对象
rs=pstatement.executeQuery();
while(rs.next()){
System.out.print(rs.getInt(1));
System.out.print(rs.getString(2));
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}
//6.关闭连接
dbManager.closeResource(conn, pstatement, rs);
}
}
例6、PreparedStatement防止SQL注入
第一步、建表login
第二步、用Statement模拟登陆。
package vs;
import java.sql.*;
import cn.itcast.mysql.util.DBManager;
public class TestStatementResultSet {
public static void main(String[] args){
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
int count=0;
dbManager=new DBManager();
String username="zhangsan' OR 1=1 OR 1='";
String passw="123456";
conn=dbManager.getConnection();
String sql="SELECT COUNT(*) FROM login WHERE username='"+username+"' AND passw='"+passw+"'";
System.out.println(sql);
try {
statement=conn.createStatement();
rs = statement.executeQuery(sql);
while(rs.next()){
count=rs.getInt(1);
}
if(count>0){
System.out.println("登陆成功!");
}else{
System.out.println("登录失败!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, statement, rs);
}
}
}
执行结果为:
SELECT COUNT(*) FROM login WHERE username='zhangsan' OR 1=1 OR 1='' AND passw='123456'
登陆成功!
第三步、用PreparedStatement模拟登陆。
package vs;
import java.sql.*;
import cn.itcast.mysql.util.DBManager;
public class TestStatementResultSet {
public static void main(String[] args){
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
ResultSet rs=null;
int count=0;
dbManager=new DBManager();
String username="zhangsan' OR 1=1 OR 1='";
String passw="123456";
conn=dbManager.getConnection();
String sql="SELECT COUNT(*) FROM login WHERE username=? AND passw=?";
System.out.println(sql);
try {
pstatement=conn.prepareStatement(sql);
pstatement.setString(1, username);
pstatement.setString(2, passw);
rs = pstatement.executeQuery();
while(rs.next()){
count=rs.getInt(1);
}
if(count>0){
System.out.println("登陆成功!");
}else{
System.out.println("登录失败!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, rs);
}
}
运行结果:
SELECT COUNT(*) FROM login WHERE username=? AND passw=? ①
登录失败!
注意:在PreparedStatement中打印的sql语句始终为 ①
例6、模糊查询:Statement 类似案例:
第一步、建表
第二步、三种查询方法(第二种最优)
package cn.itcast.mysql.search;
import java.sql.*;
import cn.itcast.mysql.util.DBManager;
public class TestStatement {
public static void main(String[] args) {
method3();
}
/***********************************************************************************************/
public static void method1(){
//模拟web开发当中的环境
String username=null;//request.getParameter("username");
String sex=null;
String edu=null;
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
dbManager=new DBManager();
conn=dbManager.getConnection();
//增加查询条件,设置查询的值
username=null;
sex="男";
edu="大专";
/*
* 处理页面提交的数据,不用出现空值null,但可以使用""
* *客户端提交过来的数据有如下几种情况
* * 获取到的值为null,不作为查询条件
* * 获取的值为"",不作为查询条件
* * 不是上面的两种情况,作为查询条件
*/
if(username==null || "".equals(username.trim())){
username="";
}
if(sex==null || "".equals(sex.trim())){
sex="";
}
if(edu==null || "".equals(edu.trim())){
edu="";
}
//组织SQL语句
String sql="SELECT id,username,sex,edu,realname," +
"role FROM employees WHERE username LIKE '%"+username+"%' and sex like '%"+sex+"%' and edu like '%"+edu+"%'";
System.out.println(sql);
try {
statement=conn.createStatement();
rs=statement.executeQuery(sql);
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.println(rs.getString(2)+" ");
System.out.println(rs.getString(3)+" ");
System.out.println(rs.getString(4)+" ");
System.out.println(rs.getString(5)+" ");
System.out.println();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, statement, rs);
}
}
/***********************************************************************************************/
public static void method2(){
String username=null;
String sex=null;
String edu=null;
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
dbManager=new DBManager();
conn=dbManager.getConnection();
username="A";
sex="";
edu="大专";
//组织查询条件
String whereSql="";
//是否增加where的标志,false表示没有where ,true表示存在where
boolean whereFlag=false;
if(username!=null && !("".equals(username.trim()))){
if(!whereFlag){//不存在where
whereSql=whereSql+" where username = '"+username.trim()+"'";
whereFlag=true;//更改标志位true
}else{//存在where
whereSql=whereSql+" and username = '"+username.trim()+"'";
}
}
if(sex!=null && !("".equals(sex.trim()))){
if(!whereFlag){//不存在where
whereSql=whereSql+" where sex = '"+sex.trim()+"'";
whereFlag=true;//更改标志位true
}else{//存在where
whereSql=whereSql+" and sex = '"+sex.trim()+"'";
}
}
if(edu!=null && !("".equals(edu.trim()))){
if(!whereFlag){//不存在where
whereSql=whereSql+"where edu = '"+edu.trim()+"'";
whereFlag=true;//更改标志位true
}else{//存在where
whereSql=whereSql+" and edu = '"+edu.trim()+"'";
}
}
String sql="SELECT id,username,sex,edu,realname,role FROM employees ";
sql=sql+whereSql;
System.out.println(sql);
try {
statement=conn.createStatement();
rs=statement.executeQuery(sql);
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");
System.out.print(rs.getString(5)+" ");
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, statement, rs);
}
}
/***********************************************************************************************/
public static void method3(){
String username=null;
String sex=null;
String edu=null;
DBManager dbManager=null;
Connection conn=null;
Statement statement=null;
ResultSet rs=null;
dbManager=new DBManager();
conn=dbManager.getConnection();
username="A";
sex="";
edu="大专";
String whereSql="";
if(username!=null && !("".equals(username.trim()))){
whereSql=whereSql+" and username = '"+username.trim()+"'";
}
if(sex!=null && !("".equals(sex.trim()))){
whereSql=whereSql+" and sex = '"+sex.trim()+"'";
}
if(edu!=null && !("".equals(edu.trim()))){
whereSql=whereSql+" and edu = '"+edu.trim()+"'";
}
String sql="SELECT id,username,sex,edu,realname,role FROM employees where 1=1";
sql=sql+whereSql;
System.out.println(sql);
try {
statement=conn.createStatement();
rs=statement.executeQuery(sql);
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");
System.out.print(rs.getString(5)+" ");
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, statement, rs);
}
}
}
例7、模糊查询:PreparedStatement 实现查询,通用模型。
方法一、
package cn.itcast.mysql.search;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import cn.itcast.mysql.util.DBManager;
public class TestPreparedStatement {
public static void main(String[] args) {
method4();
}
public static void method4(){
String username=null;
String sex=null;
String edu=null;
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
ResultSet rs=null;
dbManager=new DBManager();
conn=dbManager.getConnection();
//username="A";
sex="";
edu="大专";
//存放查询条件的sql语句 ? ? ?
String whereSql="";
//list存放的是?的值 有序
List paramlist=new ArrayList();
if(username!=null && !("".equals(username.trim()))){
whereSql=whereSql+" and username like ?"; //1
paramlist.add("%"+username.trim()+"%"); //0
}
if(sex!=null && !("".equals(sex.trim()))){
whereSql=whereSql+" and sex =?";
paramlist.add(sex);
}
if(edu!=null && !("".equals(edu.trim()))){
whereSql=whereSql+" and edu =?"; //2
paramlist.add(edu); //1
}
Object[] params=paramlist.toArray();
String sql="SELECT id,username,sex,edu,realname,role FROM employees where 1=1";
sql=sql+whereSql;
System.out.println(sql);
try {
pstatement=conn.prepareStatement(sql);
if(params!=null && params.length>0){
for(int i=0;i<params.length;i++){
System.out.println("****"+params[i].toString());
pstatement.setObject(i+1, params[i]);
}
}
rs=pstatement.executeQuery();
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");
System.out.print(rs.getString(5)+" ");
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, rs);
}
}
}
运行结果为:
SELECT id,username,sex,edu,realname,role FROM employees where 1=1 and edu =?
****大专
5 A 男 大专 王芳
6 A 男 大专 张恒
方法二、
package cn.itcast.mysql.search;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import cn.itcast.mysql.util.DBManager;
public class TestPreparedStatement {
public static void main(String[] args) {
method5();
}
public static void method4(){
String username=null;
String sex=null;
String edu=null;
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
ResultSet rs=null;
dbManager=new DBManager();
conn=dbManager.getConnection();
//username="A";
sex="";
edu="大专";
//存放查询条件的sql语句 ? ? ?
String whereSql="";
//list存放的是?的值 有序
List paramlist=new ArrayList();
if(username!=null && !("".equals(username.trim()))){
whereSql=whereSql+" and username like ?"; //1
paramlist.add("%"+username.trim()+"%"); //0
}
if(sex!=null && !("".equals(sex.trim()))){
whereSql=whereSql+" and sex =?";
paramlist.add(sex);
}
if(edu!=null && !("".equals(edu.trim()))){
whereSql=whereSql+" and edu =?"; //2
paramlist.add(edu); //1
}
Object[] params=paramlist.toArray();
String sql="SELECT id,username,sex,edu,realname,role FROM employees where 1=1";
sql=sql+whereSql;
System.out.println(sql);
try {
pstatement=conn.prepareStatement(sql);
if(params!=null && params.length>0){
for(int i=0;i<params.length;i++){
System.out.println("****"+params[i].toString());
pstatement.setObject(i+1, params[i]);
}
}
rs=pstatement.executeQuery();
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");
System.out.print(rs.getString(5)+" ");
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, rs);
}
}
public static void method5(){
String username=null;
String sex=null;
String edu=null;
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
ResultSet rs=null;
dbManager=new DBManager();
conn=dbManager.getConnection();
//username="A";
sex="";
edu="大专";
//存放查询条件的sql语句 ? ? ?
String whereSql="";
//list存放的是?的值 有序
List paramlist=new ArrayList();
//typeList存放查询的数据类型
List<Integer> typeList=new ArrayList<Integer>();
if(username!=null && !("".equals(username.trim()))){
whereSql=whereSql+" and username like ?"; //1
paramlist.add("%"+username.trim()+"%"); //0
typeList.add(java.sql.Types.VARCHAR);
}
if(sex!=null && !("".equals(sex.trim()))){
whereSql=whereSql+" and sex =?";
paramlist.add(sex);
typeList.add(java.sql.Types.VARCHAR);
}
if(edu!=null && !("".equals(edu.trim()))){
whereSql=whereSql+" and edu =?"; //2
paramlist.add(edu); //1
typeList.add(java.sql.Types.VARCHAR);
}
Object[] params=paramlist.toArray();
//报数据类型存放到一个整形数组中
Integer[] type=new Integer[typeList.size()];
typeList.toArray(type);
String sql="SELECT id,username,sex,edu,realname,role FROM employees where 1=1";
sql=sql+whereSql;
System.out.println(sql);
try {
pstatement=conn.prepareStatement(sql);
if(params!=null && params.length>0){
for(int i=0;i<params.length;i++){
System.out.println("****"+params[i].toString());
pstatement.setObject(i+1, params[i],type[i]);
}
}
rs=pstatement.executeQuery();
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");
System.out.print(rs.getString(5)+" ");
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, rs);
}
}
}
方法三、(重点,实际开发中用)
package cn.itcast.mysql.search;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import cn.itcast.mysql.util.DBManager;
public class TestPreparedStatement {
public static void main(String[] args) {
method6();
}
public static void method6(){
String username=null;
String sex=null;
String edu=null;
String sbeginHiredate=null;
String sendHiredate=null;
DBManager dbManager=null;
Connection conn=null;
PreparedStatement pstatement=null;
ResultSet rs=null;
dbManager=new DBManager();
conn=dbManager.getConnection();
username="";
sex="男";
edu="";
sbeginHiredate="2001-01-01";
sendHiredate="2004-09-09";
Date beginHireDate=null;
Date endHireDate=null;
beginHireDate=java.sql.Date.valueOf(sbeginHiredate);
endHireDate=java.sql.Date.valueOf(sendHiredate);
/*
* 业务要求:
* *开始的雇用日期和结束的雇佣日期这个时间段不能为null;如为null则不作为查询条件
* *用户名 模糊查询
* *性别 =查询
* *学历 =查询
*/
//存放查询条件的sql语句 ? ? ?
String whereSql="";
//list存放的是?的值 有序
List paramlist=new ArrayList();
if(username!=null && !"".equals(username.trim())){
whereSql=whereSql+" and username like ?";
paramlist.add("%"+username.trim()+"%");
}
if(sex!=null && !"".equals(sex.trim())){
whereSql=whereSql+" and sex=?";
paramlist.add(sex.trim());
}
if(edu!=null && !"".equals(edu.trim())){
whereSql=whereSql+" and edu=?";
paramlist.add(edu.trim());
}
if(beginHireDate!=null && endHireDate!=null){
whereSql = whereSql+" and hiredate between ? and ?";
paramlist.add(beginHireDate);
paramlist.add(endHireDate);
}
Object[] params=paramlist.toArray();
String sql="SELECT id,username,sex,edu,realname,role FROM employees where 1=1";
sql=sql+whereSql;
System.out.println(sql);
try {
pstatement=conn.prepareStatement(sql);
if(params!=null && params.length>0){
for(int i=0;i<params.length;i++){
System.out.println("****"+params[i].toString());
pstatement.setObject(i+1, params[i]);
}
}
rs=pstatement.executeQuery();
while(rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.print(rs.getString(3)+" ");
System.out.print(rs.getString(4)+" ");
System.out.print(rs.getString(5)+" ");
System.out.println();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, rs);
}
}
}
例8、JDBC的事务,模拟银行存款系统。
第一步、建表。
CREATE TABLE account
(
accountid VARCHAR(18), #账号
balance DOUBLE(10,2) #余额
)
CREATE TABLE inaccount
(
accountid VARCHAR(18), #账号
inbalance DOUBLE(10,2) #存入金额
)
第二步、
package cn.itcast.mysql.trans;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.itcast.mysql.util.DBManager;
public class TestTrans {
public static void main(String[] args) {
//获取存入的金额和存款的账号
double inbalance=3000;//request.getParameter("inbalance");
String accountid="1234";
DBManager dbManager=new DBManager();
Connection conn=null;
PreparedStatement pstatement=null;
ResultSet rs=null;
conn=dbManager.getConnection();
try {
/*
* 获取当前Connection对象的自动提交模式
* true 表示事务自动提交
* false 表示事务手动提交
*/
/*************************************************************************************************/
System.out.println(conn.getAutoCommit());
//更改事务的提交方式
conn.setAutoCommit(false);
System.out.println(conn.getAutoCommit());
/*************************************************************************************************/
//存款,插入存款信息表
String sqlInsert="insert into inaccount(accountid,inbalance) values(?,?)";
pstatement=conn.prepareStatement(sqlInsert);
pstatement.setString(1, accountid);
pstatement.setDouble(2, inbalance);
pstatement.executeUpdate();
//以账号为标准在账户基本信息表中查询余额
String sqlSelect="select balance from account where accountid=?";
pstatement=conn.prepareStatement(sqlSelect);
pstatement.setString(1, accountid);
rs=pstatement.executeQuery();
double balance=0;
if(rs.next()){
balance=rs.getDouble(1);
}
//计算新的余额
balance=balance+inbalance;
/*
* 模拟网络异常(代码到此处出现异常,后面的代码没有执行,那么最后的结果就是,\
* 我存入了钱,但是账户余额没变)
*/
//int k=5/0;
//更新账户基本信息表
String sqlUpdate="update account set balance=? where accountid=?";
pstatement=conn.prepareStatement(sqlUpdate);
pstatement.setString(2, accountid);
pstatement.setDouble(1, balance);
pstatement.executeUpdate();
/*************************************************************************************************/
//是上一次提交/回滚后进行的更改成为持久更改
conn.commit();
/*************************************************************************************************/
} catch (Exception e) {
System.out.println("出现异常");
try {
if(conn!=null){
//取消在当前事务中进行的所有更改,并释放此Connection对象当前持有的数据库锁,此方法只应该在禁用事务自动提交时使用,这个if很重要!!!
conn.rollback();
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}finally{
dbManager.closeResource(conn, pstatement, rs);
}
}
}
总结:如何处理jdbc中的事务
1、 设置连接的提交方式为手动
2、 执行n各insert或update语句
3、 提交事务
Conn.commit();
4、 出现异常处理回滚
Conn.rollback();
注意:上面的所有操作必须在同一个连接中使用,跨连接不支持
例9、jdbc批量处理——–批量插入数据:oracle数据库的使用。
Mysql数据库处理批处理的速度很慢!!!!
第一步、安装orale,建表
create table test
(
id number(6),
name varchar2(18),
email varchar(30)
)
第二步、创建工具类
package cn.itcast.oracle.util;
import java.sql.*;
//数据库管理类
public class DBManager {
private String className="oracle.jdbc.driver.OracleDriver";
private String username="scott";
private String password="tiger";
private String url="jdbc:oracle:thin@localhost:1521:orcl";
//1.注册驱动
public DBManager(){
try {
Class.forName(className);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//2.获取连接
public Connection getConnection(){
Connection conn=null;
try {
conn=DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//3.关闭资源
public void closeResource(Connection conn,Statement statement,ResultSet rs){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(statement!=null){
statement.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
第三步、批量处理类
方法一、用Statement处理
package cn.itcast.oracle.batch;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import cn.itcast.oracle.util.DBManager;
public class TestStatement {
public static void main(String[] args) {
/*
* 插入5000条记录
* create table test
(
id number(6),
name varchar2(18),
email varchar(30)
)
*/
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
Statement statement=null;
try {
long begintime=System.currentTimeMillis();
statement=conn.createStatement();
for(int i=1;i<5001;i++){
//组织SQL语句
String name="name"+i;
String email="email"+i;
String sql="insert into test(id,name,email) values("+i+",'"+name+"','"+email+"')";
//将给定的 SQL 命令添加到此 Statement 对象的当前命令列表中
statement.addBatch(sql);
if(i%1000==0){
//将一批命令提交给数据库来执行,如果全部命令执行成功,则返回更新计数组成的数组
statement.executeBatch();
//清空此statement对象的当前SQL命令列表
statement.clearBatch();
}
}
long endtime=System.currentTimeMillis();
System.out.println(endtime-begintime);
System.out.println((endtime-begintime)/1000);
} catch (SQLException e) {
e.printStackTrace();
}finally{
dbManager.closeResource(conn, statement, null);
}
}
}
运行结果:
6536
6
方法二、用PreparedStatement处理(带事务处理)
package cn.itcast.oracle.batch;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import cn.itcast.oracle.util.DBManager;
public class TestPreparedStatement {
public static void main(String[] args) {
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
PreparedStatement pstatement=null;
try {
long begintime=System.currentTimeMillis();
String sql="insert into test(id,name,email) values(?,?,?)";
conn.setAutoCommit(false);
pstatement=conn.prepareStatement(sql);
for(int i=1;i<5001;i++){
pstatement.setInt(1, i);
pstatement.setString(2, "name"+i);
pstatement.setString(3, "email"+i);
pstatement.addBatch();
if(i%1000==0){
pstatement.executeBatch();
pstatement.clearBatch();
}
}
conn.commit();
long endtime=System.currentTimeMillis();
System.out.println(endtime-begintime);
} catch (SQLException e) {
e.printStackTrace();
try {
if(conn!=null){
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
dbManager.closeResource(conn, pstatement, null);
}
}
}
运行结果:
172
0
例10、jdbc处理大对象和获取元数据。向数据库中存入一张图片。
第一步、建表
CREATE TABLE pic
(
id INT AUTO_INCREMENT PRIMARY KEY,
pic MEDIUMBLOB
)
第二步、创建java类
package cn.itcast.mysql.blob;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import cn.itcast.mysql.util.DBManager;
public class TestBlob {
public static void main(String[] args) {
TestBlob b=new TestBlob();
b.savepic();
}
public void savepic(){
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
PreparedStatement pstatement=null;
String sql="insert into pic(id,pic) values(?,?)";
try {
FileInputStream fis;
fis = new FileInputStream(new File("F:\\Lianxi\\day13jdbc\\src\\cn\\itcast\\mysql\\blob\\1.jpg"));
//在左侧图片名称上面点击右键,选择properties在显示的面板中间可以看到Location
pstatement=conn.prepareStatement(sql);
pstatement.setInt(1, 1);
pstatement.setBlob(2, fis);
pstatement.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, null);
}
}
}
第三步、运行上面的java类,将图片存入数据库中,便可以看到数据的效果了!
点击表中的图片表单元便可看到图片!!!
第四步、读取图片
package cn.itcast.mysql.blob;
import java.io.*;
import java.sql.*;
import cn.itcast.mysql.util.DBManager;
public class TestBlob {
public static void main(String[] args) {
TestBlob b=new TestBlob();
b.readpic();
}
//读取图片
public void readpic(){
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
PreparedStatement pstatement=null;
ResultSet rs=null;
Blob blob=null;
String sql="select pic from pic where id=?";
try {
pstatement=conn.prepareStatement(sql);
pstatement.setInt(1, 1);
rs = pstatement.executeQuery();
if(rs.next()){
blob = rs.getBlob(1);
}
//构造文件输出流
FileOutputStream fos=new FileOutputStream("F:\\Lianxi\\day13jdbc\\src\\cn\\itcast\\mysql\\blob\\xxx.jpg");
//构造缓冲输出流
BufferedOutputStream bos=new BufferedOutputStream(fos);
if(blob!=null){
//获取读取图片的输入流
InputStream in = blob.getBinaryStream();
//转化为缓冲输入流
BufferedInputStream bis=new BufferedInputStream(in);
//从输入流中获取数据,写入到输出流
int len=0;
byte[] b=new byte[1024];
while((len = bis.read(b))!=-1){
//写入到输出流
bos.write(b,0,len);
}
bos.close();
bis.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, null);
}
}
}
第五步、运行,查看结果:
运行上面的程序以后,将xxx.jpg拷贝到桌面打开就可以看见了!!!!!
例11、获取元数据. DatabaseMetaDat a
package cn.itcast.mysql.meta;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import cn.itcast.mysql.util.DBManager;
public class TestDataBaseMetaData {
public static void main(String[] args) {
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
//获取一个 DatabaseMetaData 对象,该对象包含关于此 Connection 对象所连接的数据库的元数据。
try {
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println(dbmd);
//关于数据库的整体综合信息。
System.out.println("数据库URL"+dbmd.getURL());
System.out.println("数据库名称"+dbmd.getDatabaseProductName());
System.out.println("数据库版本"+dbmd.getDatabaseProductVersion());
System.out.println("数据库驱动名称"+dbmd.getDriverName());
System.out.println("数据库驱动版本"+dbmd.getDriverVersion());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
}
}
运行结果:
com.mysql.jdbc.JDBC4DatabaseMetaData@1db699b
数据库URLjdbc:mysql://localhost:3306/test
数据库名称MySQL
数据库版本5.0.67-community-nt
数据库驱动名称MySQL-AB JDBC Driver
数据库驱动版本mysql-connector-java-5.1.10 ( Revision: ${svn.Revision} )
例12、获取元数据. ResultSetMetaData.
第一步、建表 employees
第二步、
package cn.itcast.mysql.meta;
import java.sql.*;
import cn.itcast.mysql.util.DBManager;
public class TestResultSetMetaData {
public static void main(String[] args) {
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
PreparedStatement pstatement=null;
ResultSet rs=null;
try {
String sql="SELECT id,username,sex,edu FROM employees";
pstatement=conn.prepareStatement(sql);
rs=pstatement.executeQuery();
//获取结果集的元数据,获取此 ResultSet 对象的列的编号、类型和属性。
ResultSetMetaData rmd= rs.getMetaData();
System.out.println("获取结果集的列数:"+rmd.getColumnCount());
System.out.println("获取指定列id的名称:"+rmd.getColumnName(1));
System.out.println("获取指定列id的类型:"+rmd.getColumnType(1));
/*
* 怎样找对应数字的类型名称:
* 在java.sql.Types中找
*/
System.out.println("获取指定列id的类型的名称:"+rmd.getColumnTypeName(1));
while(rs.next()){
for(int i=0;i<rmd.getColumnCount();i++){
System.out.print(rs.getObject(i+1)+" ");
}
System.out.println();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, null, null);
}
}
}
执行结果:
获取结果集的列数:4
获取指定列id的名称:id
获取指定列id的类型:4
获取指定列id的类型的名称:INT
1 zhangsan 男 本科
2 lisi 女 专科
3 wangwu 男 重本
4 fandong 男 重本
5 A 男 大专
6 A 男 大专
例12、将数据库中的数据封装到xml文件当中
第一步、建立一个xml文件:employees.xml
<? Xml version=”1.0” encoding=”GBK”?>
注意:将employees.xml文件的properties中的存储方式改为GBK
第二步、
package cn.itcast.mysql.meta;
import *;
public class ResultSetDataBaseMetaDataEx {
/*
* 结果集数据写入到xml文件中
* <employees>
* <employee>
* <columnName>columnValue</columnName>
* <columnName>columnValue</columnName>
* <columnName>columnValue</columnName>
* <columnName>columnValue</columnName>
* </employee>
* <employee>
* <columnName>columnValue</columnName>
* <columnName>columnValue</columnName>
* <columnName>columnValue</columnName>
* <columnName>columnValue</columnName>
* </employee>
* </employees>
*/
public static void main(String[] args) {
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
PreparedStatement pstatement=null;
ResultSet rs=null;
Document document = DocumentHelper.createDocument();
Element root = document.addElement("employees");
try {
String sql="SELECT id,username,sex,edu FROM employees";
pstatement=conn.prepareStatement(sql);
rs=pstatement.executeQuery();
ResultSetMetaData rmd = rs.getMetaData();
while(rs.next()){
Element employeeElement = root.addElement("employee");
for(int i=1;i<+rmd.getColumnCount();i++){
//获取列名
String columnName=rmd.getColumnName(i);
//获取列值
Object columnValue=rs.getObject(columnName);
employeeElement.addElement(columnName).setText(columnValue.toString());
}
}
OutputFormat format=OutputFormat.createPrettyPrint();
format.setEncoding("GBK");
String path="F:\\Lianxi\\day13jdbc\\src\\cn\\itcast\\mysql\\meta\\employees.xml";
XMLWriter writer = new XMLWriter(new FileWriter(path),format);
writer.write( document );
writer.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, pstatement, rs);
}
}
}
例13、可滚动的ResultSet结果集。以oracle数据库为例。
第一步、在oracle中建立数据库,表
CREATE TABLE test
(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(12),
email VARCHAR(12)
)
第二步、java类
import java.io.FileWriter;
import java.sql.*;
import cn.itcast.oracle.util.DBManager;
public class TestResultScroll {
public static void main(String[] args) {
DBManager dbManager=new DBManager();
Connection conn=dbManager.getConnection();
Statement statement=null;
ResultSet rs=null;
String sql="SELECT id,name,email FROM test";
try {
/*
* createStatement(int resultSetType, int resultSetConcurrency)
* *第一个参数:结果的类型 ResultSet.TYPE_FORWARD_ONLY表示指针只能向下执行
* *第二个参数:结果集的并发性 ResultSet.CONCUR_READ_ONLY表示结果集的数据不能用于更新数据
*/
statement=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY );
/*
* 注意:上面一行对MYSQL来说等同于statement=conn.createStatement();
* 但是对于oracle而言就不能等同了
*/
rs=statement.executeQuery(sql);
/* rs.next();
System.out.println(rs.getInt(1)+" "+rs.getString(2));
rs.next();
System.out.println(rs.getInt(1)+" "+rs.getString(2));
rs.previous();//表示指针返回一步,返回到第一行
System.out.println(rs.getInt(1)+" "+rs.getString(2));
//将光标移到最后一行
rs.last();
System.out.println(rs.getInt(1)+" "+rs.getString(2));
//将光标移到第一行
rs.first();
System.out.println(rs.isFirst());//判断光标是否在第一行
System.out.println(rs.getInt(1)+" "+rs.getString(2));
//将光标移到第一行之前,然后输出第一行的值
rs.beforeFirst();
rs.first();
System.out.println(rs.getInt(1)+" "+rs.getString(2));
//将光标移到最后一行的下面,然后输出最后一行的值
rs.afterLast();
rs.next();
System.out.println(rs.getInt(1)+" "+rs.getString(2)); */
//移动到最后一行,获取总的行数
rs.last();
System.out.println("结果集的行数:"+rs.getRow());
// 将光标移动到此 ResultSet 对象的给定行编号。
rs.absolute(3);
System.out.println(rs.getInt(1)+" "+rs.getString(2));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
dbManager.closeResource(conn, statement, rs);
}
}
}
第三步、运行
JUNIT:
Junit3:
1、在3.8版本的风格下,左右完成测试功能的类要继承TestCast类,所有要测试的方法都要使用testXxx的命名方式,之后由TestRunner类来运行测试类中的每一个需要测试的方法,TestCase类继承了Assert类,有很多static
assertXxx方法,每个方法勇于断言不同的情况。
Junit中常用的assert方法
Errors:表示在调用断言函数之前就已经错误了,例如:数组越界异常等RuntimeException
Failures:assert函数没通过
3、开发步骤、
第一步、
package cn.itcast.junit;
public class Computer {
public int add(int a, int b){
int k=a+b;
k=0;
return k;
}
public int multipe(int x, int y){
int k=x*y;
k=0;
return k;
}
}
第二步、
package cn.itcast.junit3;
import junit.framework.Assert;
import junit.framework.TestCase;
import cn.itcast.junit.Computer;
public class TestComputer extends TestCase{
Computer c=null;
//初始化
protected void setUp() throws Exception{
System.out.println("setUp*********");
c=new Computer();
}
//回收
protected void tearDown() throws Exception{
System.out.println("tearDown*********");
}
/**
* 测试Computer类中的add方法的正确性
*/
public void testAdd(){
System.out.println("tearAdd*********");
Assert.assertEquals(0, c.add(0,0));
}
public void testMultipe(){
System.out.println("tearMultipe*********");
Assert.assertEquals(0, c.multipe(1,2));
}
}
第三步、测试:
点击右侧函数名进行测试:
测试testAdd时,结果为:
setUp*********
tearAdd*********
tearDown*********
Junit4:
1、junit4使用java5中的注解(annotation),一下是Junit4常用的几个annotation介绍:
@Before:初始化方法
@After:释放资源
@Test:测试方法,在这里可以测试期望异常和超时时间
@Ignore:忽略的测试方法
@BeforeClass:这对所有的测试,只执行一次,且必须为static void
@AfterClass:针对所有的测试,只执行一次,且必须为static void
一个JUnit4的单元测试用例的执行顺序为:
@BeforeClass->@Before->@Test->@After->@AfterClass
每一个测试方法的调用顺序为:
@Before->@Test->@After
2、开发步骤:
第一步、
package cn.itcast.junit;
public class Computer {
public int add(int a, int b){
int k=a+b;
k=0;
return k;
}
public int multipe(int x, int y){
int k=x*y;
k=0;
return k;
}
}
第二步、
package cn.itcast.junit4;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Assert;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Ignore;
import org.junit.Test;
import cn.itcast.junit.Computer;
/*
* 在junit4的版本上,
* * 测试类不需要继承TestCase
* * 使用注解
*/
public class TestComputer {
Computer c=null;
@BeforeClass
/*
* 针对所有测试之前,只执行一次,且方法为static void
*/
public static void beforeClass(){
System.out.println("beforeClass*******************");
}
/*
* 针对所有测试之后,只执行一次,且方法为static void
*/
@AfterClass
public static void afterClass(){
System.out.println("afterClass*******************");
}
//初始化
@Before
public void before(){
c=new Computer();
System.out.println("before*******************");
}
//释放资源
@After
public void after(){
System.out.println("after*******************");
}
@Test
@Ignore //忽略该方法的测试
public void add(){
System.out.println("add*******************");
Assert.assertEquals(0, c.add(1, 3));
Assert.assertEquals(3, c.add(1, 3));
}
@Test
public void multipe(){
System.out.println("add*******************");
Assert.assertEquals(0, c.multipe(1, 3));
Assert.assertEquals(3, c.multipe(1, 3));
}
}
第三步、执行:
beforeClass*******************
before*******************
add*******************
after*******************
afterClass*******************
总结:
单元测试的执行流程