mysql memory java_mysql java中的调用

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();

}

}