/*Statement中有4个执行方法可用
* 1, executeQuery() : 只能执行查询语句
* 2, executeUpdate(): 只能增、删、改, 不能执行查询语句
* 3, execute(): 增、删、改、查的语句都能够执行。只是查询时返回的结果是告诉成功与否，如果要获取查询结果，得另外用" st.getResultSet()"获取
* 4, executeBatch()
*/
@Test
public void results() throws Exception{ //res.getXXX
Statement st = ConnFactory.getConn().createStatement();
String sql = "select * from book ";
ResultSet res = st.executeQuery(sql);
while(res.next()){
Integer id = res.getInt(1);
String name = res.getString(2);
double price = res.getDouble("price");
String birth = res.getDate(4)+" "+ res.getTime(4);//注意获取日期时间型数据的方式
System.out.println(id+","+name+","+price+","+birth);
}
ConnFactory.getConn().close();
}
@Test
public void execute() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
//String sql = "insert into book(name,price,birth) values('XML',23.30,'2014-09-08 12:00:05' )";
//String sql = "update book set price=price*1.1 ";
//String sql = "delete from book where id=3";
String sql = "select * from book";
boolean boo = st.execute(sql);
System.out.println(boo);
if(boo){
ResultSet rs = st.getResultSet();
while(rs.next()){
System.out.println(rs.getInt(1)+","+rs.getString(2));
}
}
}
@Test
public void executeUpdate() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
//String sql = "insert into book(name,price,birth) values('红楼梦',85.66,'2013-10-08 12:00:05' )";
//String sql = "update book set price=price*1.1 ";
//String sql = "delete from book where id=1";
String sql = "select * from book";
int num = st.executeUpdate(sql);//返回值是影响的行数
System.out.println(num);
}
@Test //容易产生bug：如输入name值为: aa,b'c
public void reg() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
int age = Integer.parseInt(sc.nextLine());
//String sql = "insert into stud values('P2001','kobe',25) ";
String sql = "insert into stud values('"+id+"','"+name+"',"+age+") ";
System.out.println(sql);
st.execute(sql);
ConnFactory.getConn().close();
}
@Test //容易被黑：如输入name值为: a' or '1'='1
public void login() throws Exception{
Statement st = ConnFactory.getConn().createStatement();
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
//String sql = "select count(*) from stud where id='P2001' and name='kobe' ";
String sql = "select count(*) from stud where id='"+id+"' and name='"+name+"' ";
System.out.println(sql);
ResultSet rs = st.executeQuery(sql);
rs.next();
int n = rs.getInt(1);
if(n<=0){
System.out.println("登录失败...");
}else{
System.out.println("登录成功....");
}
ConnFactory.getConn().close();
}
//综上：如果sql语句由程序内部直接指定，那么用Statement没问题。
//采用PrepareStatement
@Test //不会被黑：如输入name值为: a' or '1'='1
public void login2() throws Exception{
Connection con = ConnFactory.getConn();
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
//创建预处理语句对象
String sql = "select count(*) from stud where id=? and name=? ";//凡是用户输入的地方，用“?”号(称占位符)填入
PreparedStatement pst = con.prepareStatement(sql);
//给占位设置值---设置参数
pst.setString(1, id); //给第1个参数设置
pst.setString(2, name); //给第2个参数设置
ResultSet rs = pst.executeQuery();//这里不能传参数sql
rs.next();
int n = rs.getInt(1);
if(n<=0){
System.out.println("登录失败...");
}else{
System.out.println("登录成功....");
}
con.close();
}
@Test //能够防护bug：如输入name值为: aa,b'c
public void reg2() throws Exception{
Scanner sc = new Scanner(System.in);
String id = sc.nextLine();
String name = sc.nextLine();
int age = Integer.parseInt(sc.nextLine());
//String sql = "insert into stud values('P2001','kobe',25) ";
String sql = "insert into stud values(?,?,?) ";
PreparedStatement pst = ConnFactory.getConn().prepareStatement(sql);
pst.setString(1, id);
pst.setString(2, name);
pst.setInt(3, age);
pst.executeUpdate();
ConnFactory.getConn().close();
}
@Test //获取自动增长列的值
public void getAuto() throws Exception{
String sql = "insert into book(name,price,birth) values('三国演义',45.66,'2012-10-18 12:00:05' )";
Statement st = ConnFactory.getConn().createStatement();
st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet rs = st.getGeneratedKeys();
if(rs.next()){
int id = rs.getInt(1);
System.out.println("自动生成的字段值:"+id);
}
ConnFactory.getConn().close();
}
@Test //获取自动增长列的值--PreparedStatement
public void getAuto2() throws Exception{
String sql = "insert into book(name,price,birth) values(?,?,'2012-10-18 12:00:05' )";
PreparedStatement pst = ConnFactory.getConn().prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
pst.setString(1, "水流利");
pst.setDouble(2, 123.23);
pst.executeUpdate();
ResultSet rs = pst.getGeneratedKeys();
if(rs.next()){
int id = rs.getInt(1);
System.out.println("自动生成的字段值:"+id);
}
ConnFactory.getConn().close();
}
@Test //执行批处理---自己本身不带事务，如果其中某条sql挂，则后续的sql执行失败，前面的还是有效的。如果要事务，另外再采用:con.setAutoCommit(false)+try-cacth+ rollback/commit
public void batchDemo() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into book(name,price,birth) values('aaa',11.11,'2013-11-28 19:00:15' )";
Statement st = con.createStatement();
for(int i=0;i<10;i++){
if(i==5){
//sql = "insert into book(name,price,birth) values('aaa','aa','2013-11-28 19:00:15' )";
}
st.addBatch(sql);
}
sql = "update book set price =price*1.1 where price<30";
st.addBatch(sql);
int a[] = st.executeBatch();
for(int x:a){
System.out.println(x);
}
ConnFactory.getConn().close();
}
@Test //执行批处理---
public void preBatchDemo() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into book(name,price,birth) values(?,?,'2013-11-28 19:00:15' )";
PreparedStatement pst = con.prepareStatement(sql);
for(int i=0;i<10;i++){
pst.setString(1, "bb"+i);
pst.setDouble(2, 25+i);
pst.addBatch();
}
sql = "update book set price =price*1.1 where price<30";
pst.addBatch(sql);
int a[] = pst.executeBatch();
for(int x:a){
System.out.println(x);
}
ConnFactory.getConn().close();
}
}
大文件处理：
create table img(
id int,
img blob
);
TinyBlob 最大支持255
Blob 最大支持65k
MediumBlob 最大支持16M
LongBlob 最大支持4G
*/
public class LobDemoImg {
@Test //写大文件字段
public void writeLob() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "insert into img values(?,?)";
PreparedStatement pst = con.prepareStatement(sql);
pst.setInt(1, 1);
InputStream in = LobDemoImg.class.getClassLoader().getResourceAsStream("2.jpg");
pst.setBinaryStream(2, in);
pst.executeUpdate();
con.close();
}
@Test //读取大文件字段
public void readLob() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "select * from img where id=1";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
if(rs.next()){
InputStream in = rs.getBinaryStream(2);
FileOutputStream out = new FileOutputStream("d:/a/a2.jpg");
byte buf[] = new byte[512];
int len=0;
while((len=in.read(buf))!=-1){
out.write(buf, 0, len);
}
in.close();
out.close();
}
con.close();
}
存储过程处理：
@Test//不带参数
public void callProcedureDemo() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "call p1() ";
CallableStatement cst = con.prepareCall(sql);
ResultSet rs = cst.executeQuery();
while(rs.next()){
System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getInt(3));
}
}
@Test//带输入参数
public void callProcedureDemo2() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "call p2(?,?,?) ";
CallableStatement cst = con.prepareCall(sql);
cst.setString(1, "P3001");
cst.setString(2, "芙蓉姐姐");
cst.setInt(3, 50);
ResultSet rs = cst.executeQuery();
while(rs.next()){
System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getInt(3));
}
}
@Test//带输入输出参数-----第4个参数 是 输出参数
public void callProcedureDemo3() throws Exception{
Connection con = ConnFactory.getConn();
String sql = "call p3(?,?,?,?) ";
CallableStatement cst = con.prepareCall(sql);
cst.setString(1, "P3002");
cst.setString(2, "凤姐");
cst.setInt(3, 40);
cst.registerOutParameter(4, Types.INTEGER);
cst.execute();
int count = cst.getInt(4);
System.out.println(count);
}
转载请注明原文地址: https://ju.6miu.com/read-1304062.html
