package com.inco.hive.lytest;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.*;
public class connet_mysql {
public static void main(String[] args) {
connet_mysql connet=new connet_mysql();
try {
/*connet.createsql();*/
/* connet.savepoint();*/
/*connet.update();*/
/* connet.select();*/
/* connet.insert();*/
/* connet.selectject();*/
/* connet.insertpic();*/
/* connet.readtpic();*/
/* connet.pro();*/
connet.functio2();
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection(){
Connection connection=null;
try {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//数据库连接url
String url="jdbc:mysql://localhost:3306/bigdata";
String user="root";
String password="123456";
connection= DriverManager.getConnection(url,user,password);
}
catch (Exception ee){
ee.printStackTrace();
}
return connection;
}
public void createsql() {
Connection con=null;
Statement st=null;
try {
con = getConnection();
//不自动提交
con.setAutoCommit(false);
st=con.createStatement();
/*st.execute("delete from mytableV where id=2");*/
st.execute("insert into mytableV (id,nane)values (1,'jerry')");
st.execute("insert into mytableV (id,nane)values (2,'jek')");
//手动提交事务
con.commit();
st.close();
con.close();
System.out.println("insert over");
}
catch (Exception ee){
ee.printStackTrace();
//手动提交的话有异常就回滚
try {
con.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
finally {
try {
if(st!=null &&!st.isClosed()){
st.close();
}
if(con!=null &&!con.isClosed()){
con.close();
}
}
catch (Exception ee){
ee.printStackTrace();
}
}
}
//保存点
public void savepoint(){
Connection con=null;
Statement st=null;
try {
con = getConnection();
//不自动提交
con.setAutoCommit(false);
st = con.createStatement();
st.execute("insert into mytableV (id,nane)values (4,'4')");
Savepoint s1=con.setSavepoint("1");
st.execute("insert into mytableV (id,nane)values (5,'5')");
Savepoint s2=con.setSavepoint("2");
st.execute("insert into mytableV (id,nane)values (6,'6')");
Savepoint s3=con.setSavepoint("3");
con.rollback(s2);
con.commit();
st.close();
con.close();
System.out.println("insert 123");
}
catch (Exception ee){
ee.printStackTrace();
}
}
public void update() throws Exception{
Connection con=null;
Statement st=null;
con = getConnection();
//不自动提交
con.setAutoCommit(false);
st = con.createStatement();
st.execute("update mytableV set nane='test' where id='1'");
con.commit();
st.close();
con.close();
System.out.println("update 123");
}
public void select() throws Exception{
Connection con=null;
Statement st=null;
con = getConnection();
//不自动提交
con.setAutoCommit(false);
st = con.createStatement();
ResultSet rs=st.executeQuery("select * from mytableV");
while (rs.next()){
String id=rs.getString("id");
String name=rs.getString("nane");
//如果有年龄0岁这样的，用Integer强转。即可得到
/*Integer age =(Integer)rs.getObject("age");*/
System.out.println("id:"+id+" "+"name:"+name);
}
con.commit();
st.close();
con.close();
System.out.println("select 123");
}
//提交一个事务一次性插入100万数据
public void insert() throws Exception{
int max=9998;
Connection con=null;
PreparedStatement pst=null;
con = getConnection();
con.setAutoCommit(false);
long start=System.currentTimeMillis();
//绑定参数
pst = con.prepareStatement("insert into mytableC (id,nane) values (?,?)");
int count=0;
for(int i = 1;i<=max;i++){
pst.setInt(1,i);
pst.setString(2,"tom"+i);
//每1999个攒一个批次
pst.addBatch();
count ++;
//执行批次
if(count==1999){
pst.executeBatch();
pst.clearBatch();
count =0;
}
if(count!=999 && i==max){
pst.executeBatch();
pst.clearBatch();
count =0;
}
}
con.commit();
System.out.println(System.currentTimeMillis()-start);
pst.close();
con.close();
}
public void selectject() throws Exception{
Connection con=null;
PreparedStatement pst=null;
con = getConnection();
con.setAutoCommit(false);
int i=0;
pst = con.prepareStatement("select * from mytableV where id = ? and nane = ?");
//绑定参数
pst.setString(1,"1' or 1=1 --");
pst.setString(2,"tom");
ResultSet rs=pst.executeQuery();
while (rs.next()){
String id=rs.getString("id");
String name=rs.getString("nane");
}
//执行更新
con.commit();
pst.close();
con.close();
}
//插入图片
public void insertpic() throws Exception{
Connection con=null;
PreparedStatement pst=null;
con = getConnection();
con.setAutoCommit(false);
long start=System.currentTimeMillis();
//绑定参数
pst = con.prepareStatement("insert into mytablepic (id,pic,nane) values (?,?,?)");
pst.setInt(1,1);
File file=new File("D:/123.jpg");
FileInputStream fis= new FileInputStream(file);
pst.setBinaryStream(2,fis,file.length());
pst.setString(3,"tom");
pst.executeUpdate();
con.commit();
System.out.println(System.currentTimeMillis()-start);
pst.close();
con.close();
}
//读取pic
public void readtpic() throws Exception{
Connection con=null;
PreparedStatement pst=null;
con = getConnection();
con.setAutoCommit(false);
long start=System.currentTimeMillis();
//绑定参数
String sql="select pic from mytablepic where id=? ";
pst = con.prepareStatement(sql);
//(1,1) 后面的参数即是上面的sql里面的值
pst.setInt(1,1);
ResultSet st=pst.executeQuery();
if(st.next()){
byte[] bytes =st.getBytes(1);
FileOutputStream fis= new FileOutputStream("D:/12333.jpg");
fis.write(bytes);
fis.close();
}
con.commit();
pst.close();
con.close();
}
//存储过程
public void pro()throws Exception{
Connection con=null;
con = getConnection();
CallableStatement cst=con.prepareCall("{ call simple(?,?,?)}");
cst.setInt(1,1);
cst.setInt(2,3);
cst.registerOutParameter(3,Types.INTEGER);
cst.execute();
int s=cst.getInt(3);
System.out.println(s);
cst.close();
con.close();
}
//调用函数
public void functio2()throws Exception{
Connection con=null;
con = getConnection();
CallableStatement cst=con.prepareCall("{ ? = call functio2(?,?)}");
cst.setInt(2,3);
cst.setInt(3,3);
cst.registerOutParameter(1,Types.INTEGER);
cst.execute();
int s=cst.getInt(1);
System.out.println(s);
cst.close();
con.close();
}
}