java与数据库MySQL的学习

衔接前天：

package com.lifeng.batch;

import java.sql.Connection;

import java.sql.Statement;

import com.lifeng.jdbc.JdbcUtil;

public class BatchDemo extends JdbcUtil{

public static void main(String[] args) {

try{

Connection conn = getConnection();

 

String sql1 = "drop table if exists school";

String sql2 = "create table school (id int primary key,schoolname varchar(20))";

String sql3 = "insert into school values(2,'华商')";

String sql4 = "update school set id = 1";

Statement stmt = conn.createStatement();

stmt.addBatch(sql1);

stmt.addBatch(sql2);

stmt.addBatch(sql3);

stmt.addBatch(sql4);

stmt.executeBatch();

 

closeAll(conn,stmt,null);

}catch(Exception e){

e.printStackTrace();}}}

package com.lifeng.batch;

import java.sql.Connection;

import java.sql.PreparedStatement;

import com.lifeng.jdbc.JdbcUtil;

public class BatchDemo02 extends JdbcUtil{

public static void main(String[] args) {

try{

Connection conn = getConnection();

 

String sql = "insert into school values(?,?)";

PreparedStatement pstmt = conn.prepareStatement(sql);

for(int i = 0;i < 100;i++) {

pstmt.setInt(1, i+1);

pstmt.setString(2, "深大"+i);

pstmt.addBatch();

}

pstmt.executeBatch();

closeAll(conn,pstmt,null);

}catch(Exception e){

e.printStackTrace();}}}

 

package com.lifeng.increment;

import java.sql.Connection;

import java.sql.Statement;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import com.lifeng.jdbc.JdbcUtil;

public class Demo01 extends JdbcUtil {

public static void main(String[] args) {

try{

Connection conn = getConnection();

 

String sql = "insert into dept(name) values(?)";

PreparedStatement pstmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

pstmt.setString(1, "人事部");

pstmt.executeUpdate();

 

ResultSet rs = pstmt.getGeneratedKeys();

int deptno = 0;

if(rs.next()) {

deptno = rs.getInt(1);

}

 

 

String sql2 = "insert into emp values(?,?,?,?,?,?,?,?)";

pstmt = conn.prepareStatement(sql2);

pstmt.setInt(1, 7940);

pstmt.setString(2, "aaa");

pstmt.setString(3, "cleaner");

pstmt.setInt(4, 7902);

pstmt.setDate(5, new java.sql.Date(System.currentTimeMillis()));

pstmt.setDouble(6, 2000.00);

pstmt.setDouble(7,200.00);

pstmt.setInt(8, deptno);

pstmt.executeUpdate();

 

closeAll(conn,pstmt,null);

}catch(Exception e){

e.printStackTrace();}}}

package com.lifeng.increment;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import com.lifeng.jdbc.JdbcUtil;

import com.mysql.jdbc.Statement;

import oracle.net.aso.p;

public class Demo02 {

public static void main(String[] args) {

JdbcUtil bd = new JdbcUtil();

Connection conn = null;

ResultSet rs = null;

PreparedStatement pstmt = null;

try{

conn = bd.getConnection();

String deptSql = "insert into dept(name) values(?)";

String empSql = "insert into emp(ename,deptno)  values(?,?)";

/*

* 如何获取自增长的值

* 1.设置是否可以返回自增长的值

* RETURN_GENERATED_KEYS： 可以返回自动增长值

* NO_GENERATED_KEYS:不能返回自动增长的值

*/

//参数赋值

pstmt  = conn.prepareStatement(deptSql,Statement.RETURN_GENERATED_KEYS);

pstmt.setString(1,"人事部");

pstmt.executeUpdate();

 

/*

* 获取增长的值

* 注意：该ResultSet 只包含了增长的字段

*/

rs = pstmt.getGeneratedKeys();

int deptId = 0;

if(rs.next()) {

deptId = rs.getInt(1);

}

 

pstmt = conn.prepareStatement(empSql);

pstmt.setString(1,"李四");

pstmt.setInt(2,deptId);

//执行插入员工的sql

pstmt.executeUpdate();

System.out.println("为该部门添加员工成功");

}catch(Exception e) {

e.printStackTrace();

}finally {}}}

package com.lifeng.lob;

import java.io.FileWriter;

import java.io.Reader;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import com.lifeng.jdbc.JdbcUtil;

public class ClobDemo01 extends JdbcUtil{

public static void main(String[] args) {

ReadClob();

 

}

private static void ReadClob() {

Connection conn = null;

ResultSet rs = null;

PreparedStatement pstmt = null;

try {

conn = getConnection();

String sql = "select * from news where id = 1";

pstmt = conn.prepareStatement(sql);

//执行查询

rs = pstmt.executeQuery();

//读取结果集

if(rs.next()) {

//获取text·字段

//1.可以吧text字段内容当做普通字符串读取

// String content = rs.getString("content");

//System.out.println(content);

 

//MySQL  text ，Oracle clob

//2.可以把text字段当做输入流读取

Clob clob = rs.getClob("content");

//通过clob字段读取输入流

Reader reader = clob.getCharacterStream();

char [] buf = new char[1024];

//把输入流输出到文件中

FileWriter writer = new FileWriter("d:/abc.txt");

int len = 0;

while((len = reader.read(buf)) != -1) {

//边读边写出

writer.write(buf,0,len);

}

writer.close();

reader.close();

}

 

}catch(Exception e) {

e.printStackTrace();

}finally {}}}

package com.lifeng.lob;

import java.io.FileOutputStream;

import java.io.FileReader;

import java.io.InputStream;

import java.io.Reader;

import java.sql.Blob;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import com.lifeng.jdbc.JdbcUtil;

public class ClobDemo03 extends JdbcUtil{

public static void main(String[] args) {

ReadJpg();

 

}

private static void ReadJpg() {

Connection conn = null;

ResultSet rs = null;

PreparedStatement pstmt = null;

try {

conn = getConnection();

String sql = "select * from news where id = 1";

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

if(rs.next()) {

Blob blob = rs.getBlob("attachment");

InputStream ins = blob.getBinaryStream();

byte[] buf = new byte[1024];

FileOutputStream fos = new FileOutputStream("d:/1.jpg");

nt len = 0;

while((len = ins.read(buf)) != -1) {

//边写边读

fos.write(buf);

 

}

ins.close();

fos.close();

}

 

 

}catch(Exception e) {

e.printStackTrace();

}finally {

closeAll(conn,pstmt,null);}}}

package com.lifeng.storedproc;

/*

 * DELIMITER $$

USE `mydb`$$

DROP PROCEDURE IF EXISTS `sp_emp5`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_emp5`(IN dno INT,OUT counts INT)

BEGIN

SELECT COUNT(*) INTO counts FROM emp WHERE deptno=dno;

END$$

DELIMITER ;

*/

import java.io.FileOutputStream;

import java.io.InputStream;

import java.sql.Blob;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import com.lifeng.jdbc.JdbcUtil;

public class StoreProcDemo01 extends JdbcUtil{

//在存入数据库时读取数据

public static void main(String[] args) {

Connection conn = null;

ResultSet rs = null;

CallableStatement pstmt = null;

try {

conn = getConnection();

String sql = "call sp_emp5(?,?)";

pstmt = conn.prepareCall(sql);

   pstmt.setInt(1, 10);

   /*

    * 1)设置输出参数

    * sqlType：就是存储过程中输出参数的数据类型

    */

 

pstmt.registerOutParameter(2,java.sql.Types.INTEGER);

pstmt.executeQuery();

/*

* 2)从输出参数中获取数据，获取方法getXXX()方法

* 注意：

* ResultSet的getXXX()方法为了获取列的值

* CallableStatement的getXXX()方法为了获取输出参数的值

*/

//结果在输出参数中，从输出参数中看结果

//这里的参数和设置输出参数的位置保持一致的

int count = pstmt.getInt(2);

System.out.println("10号部门的人数是："+count);

 

}catch(Exception e) {

e.printStackTrace();

 

}finally {

closeAll(conn,pstmt,null);}}}

今天学习了如何把文件写入数据库MySQL和读取数据库文件，一些基本的方法，内容较多，有待消化，继续加油