JDBC: JDBC常用接口
a.Driver接口:
//加载驱动类
Class.forName(“com.mysql.jdbc.Driver”);
b.DriverManager接口:
c.Connection接口:
//建立连接(连接对象内部其实包含了Socket对象,是一个远程的连接。比较耗时!这是Connection对象管理的一个要点!)
//真正开发中,为了提高效率,都会使用连接池来管理连接对象!
conn =
DriverManager.getConnection(“jdbc:mysql://localhost:3306/testjdbc”,“root”,“123456”);
d.Statement接口:
* 测试Statement接口的用法,执行SQL语句,以及SQL注入问题
stmt = conn.createStatement();
// String name = “赵六”;
// Stringsql= “insert into t_user (username,pwd,regTime) values
(’”+name+"’,66666,now())";
// stmt.execute(sql);//运行语句
//测试SQL注入
String id = "5 or 1=1 ";
String sql = “delete from t_user where id=”+id;
stmt.execute(sql);
* 测试PreparedStatement的基本用法
String sql = “insert into t_user (username,pwd,regTime) values (?,?,?)”;
//?占位符
ps = conn.prepareStatement(sql);
// ps.setString(1, “高淇3”); //参数索引是从1开始计算, 而不是0
// ps.setString(2, “123456”);
// ps.setDate(3, new java.sql.Date(System.currentTimeMillis()));
//可以使用setObject方法处理参数
ps.setObject(1, “高淇5”);
ps.setObject(2, “234567”);
ps.setObject(3,newjava.sql.Date(System.currentTimeMillis()));
// ps.execute();
intcount = ps.executeUpdate();
e.ResultSet接口:
* 测试ResultSet结果集的基本用法
String sql = “select id,username,pwd from t_user where id>?”; //?占位符
ps = conn.prepareStatement(sql);
ps.setObject(1, 2); //把id大于2的记录都取出来
rs = ps.executeQuery();//返回ResultSet结果集
while(rs.next()){
System.out.println(rs.getInt(1)+"—"+rs.getString(2)+"—"+rs.getString(3));
}
//遵循:resultset–>statment–>connection这样的关闭顺序!一定要将三个trycatch块,分开写!
f.JDBC详细操作:
* 测试批处理的基本用法
conn.setAutoCommit(false); //设为手动提交
stmt = conn.createStatement();
for(inti=0;i<20000;i++){
stmt.addBatch(“insert into t_user (username,pwd,regTime) values
('gao”+i+"’,666666,now())");
}
stmt.executeBatch();
mit(); //提交事务
* 测试事务的基本概念和用法
g.事务:
DML(data manipulation language)数据操纵语言:UPDATE、INSERT、DELETE
DDL(data definition language)数据库定义语言:CREATE、ALTERL:修改、DROP
DCL(Data Control
Language)数据库控制语言:GRANT:授权、ROLLBACK:回滚、COMMIT:提交
DQL(Data Query Language)数据库查询语言:SELECT
h.时间类型
ps.setDate(3, date);
ps.setTimestamp(4, stamp);
CLOB
ps= conn.prepareStatement("insert into t_user (username,myInfo) values
(?,?) ");
// ps.setString(1, “高淇”);
// ps.setClob(2, new FileReader(new File(“d:/a.txt”)));
//将文本文件内容直接输入到数据库中
//将程序中的字符串输入到数据库的CLOB字段中
// ps.setClob(2, new BufferedReader(new InputStreamReader(new
ByteArrayInputStream(“aaaabbbbbb”.getBytes()))));
ps = conn.prepareStatement(“select * from t_user where id=?”);
ps.setObject(1, 101024);
rs = ps.executeQuery();
while(rs.next()){
Clob c = rs.getClob(“myInfo”);
r = c.getCharacterStream();
inttemp = 0;
while((temp=r.read())!=-1){
System.out.print((char)temp);
}
j.BLOB
ps= conn.prepareStatement("insert into t_user (username,headImg) values
(?,?) ");
// ps.setString(1, “高淇”);
// ps.setBlob(2, new FileInputStream(“d:/icon.jpg”));
// ps.execute();
ps = conn.prepareStatement(“select * from t_user where id=?”);
ps.setObject(1, 101026);
rs = ps.executeQuery();
while(rs.next()){
Blob b = rs.getBlob(“headImg”);
is = b.getBinaryStream();
os =newFileOutputStream(“d:/a.jpg”);
inttemp = 0;
while((temp=is.read())!=-1){
os.write(temp);
}
s.next()){
Blob b = rs.getBlob(“headImg”);
is = b.getBinaryStream();
os =newFileOutputStream(“d:/a.jpg”);
inttemp = 0;
while((temp=is.read())!=-1){
os.write(temp);
}