Mysql基础（1）

jdbc驱动下载地址：http://mvnrepository.com/artifact/mysql/mysql-connector-java

这段代码是测试事务的基本用法以及回滚操作，其主要是要把设置为手动提交事务（setAutoCommit(false)），以及遇到异常时，通过回滚操作（rollback()），来恢复修改之前的数据！

package tk.javazhangwei.jdbc;

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

/***
 * 测试事务的概念以及使用 和回滚操作
 * 
 * @author zw
 *
 */
public class Demo07 {
	public static void main(String[] args) {
		Connection con = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","*****");
			con.setAutoCommit(false);//设置为false  自动提交事务
			
			PreparedStatement ps = con.prepareStatement("insert into students2 (name,pwd) values (?,?)");
			ps.setObject(1, "张伟");
			ps.setObject(2, 987654321);
			ps.execute();
			System.out.println("插入一条数据成功");
			try {
				Thread.sleep(5000);
			} catch (InterruptedException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			PreparedStatement ps1 = con.prepareStatement("insert into students2 (name,pwd) values (???,?)");
			ps1.setObject(1, "张伟");
			ps1.setObject(2, 987654321);
			ps1.execute();
			System.out.println("插入另一条数据成功");
			
			con.commit();//提交
			
		} catch (ClassNotFoundException e) {
			System.err.println(e);
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}//进行回滚操作
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
}
}

筛选出指定时间段的数据，并且通过io流输出到文件中。

package tk.javazhangwei.jdbc;

import java.io.BufferedWriter;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;

/***
 * 
 * <取出指定时间段的数据>
 * @author zw
 *
 */
public class Demo09 {
	/***
	 * 将字符创代表的时间格式为（yyyy-MM-dd hh:mm:ss）转为long类型
	 * @return
	 */
	public static long str2date(String date) {
		DateFormat df =new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			return df.parse(date).getTime();
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			System.out.println("解析错误，可能是输入的格式错误，请您重新输入");
		}
		return 0;
	}
	public static void main(String[] args) throws IOException {
		Connection con = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","******");
			PreparedStatement ps = con.prepareStatement("select * from students2 where regTime>? and regTime<?");
			
			Timestamp start = new Timestamp(str2date("2017-8-1 00:00:00"));
			Timestamp end = new Timestamp(str2date("2017-8-30 00:00:00"));
			ps.setObject(1, start);
			ps.setObject(2, end);
			
			rs = ps.executeQuery();
			if(!rs.next()) {
				System.out.println("没有要查询到的东西");
			}
			BufferedWriter bw = new BufferedWriter(new FileWriter(new File("d:/test/jdbc.txt")));
			while(rs.next()) {
				bw.write(rs.getInt("id")+"---"+rs.getString("name")+"---"+rs.getDate("regTime"));
				bw.newLine();
			}
			bw.flush();
			System.out.println("写入到d:/test/jdbc.txt，成功!");
			bw.close();
			ps.close();
			rs.close();
			con.close();
	
		} catch (ClassNotFoundException e) {
			System.err.println(e);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
}
}

以下两个是BLOB (binary large object)，二进制大对象和CLOB(Character Large Object) 的基本使用，其使用方法大致都一样，也是通过io流进行传输

package tk.javazhangwei.jdbc;

import java.io.File;
import java.io.FileReader;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Random;

/***
 * 测试CLOB 文本对象的使用
 * 
 * @author zw
 *
 */
public class Demo11 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","*******");
//		PreparedStatement ps = con.prepareStatement("insert into students (name,myinfo) values (?,?)");
//		ps.setString(1, "强建敏");
//		ps.setClob(2, new FileReader(new File("d:/test/myinfo.txt")));//将文本文件内容直接输入到数据库中
//		int a =ps.executeUpdate();
//		System.out.println("本次更新了"+a+"条数据库!");
		
		
		
		//取值
		PreparedStatement ps =con.prepareStatement("select * from students where id=?");
		ps.setObject(1, 5);
		
		ResultSet rs =ps.executeQuery();
		Reader r =null;
		while(rs.next()) {
			Clob c =rs.getNClob("myinfo");
			 r =c.getCharacterStream();
			int temp=0;
			while((temp=r.read())!=-1) {
				System.out.print((char)temp);
			}
		}
		r.close();
		rs.close();
		ps.close();
		con.close();
		
}
}

package tk.javazhangwei.jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Random;

/***
 * 测试处理BLOB  二进制大对象的使用
 * 
 * @author zw
 *
 */
public class Demo10 {
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc","root","*******");
//		PreparedStatement ps = con.prepareStatement("insert into students (name,headImg) values (?,?)");
//		ps.setObject(1, "杨幂");
//		ps.setBlob(2, new FileInputStream(new File("d:/test/blob.jpg")));
//		ps.execute();
		
		
		//取出
		PreparedStatement ps = con.prepareStatement("select * from students where id = ?");
		ps.setObject(1, 6);
		
		ResultSet re =ps.executeQuery();
		FileOutputStream fos = new FileOutputStream("d:/test/blobs.jpg");
		while(re.next()) {
			Blob b =re.getBlob("headImg");
			InputStream r =b.getBinaryStream();
			int temp =0;
			while((temp=r.read())!=-1) {
				fos.write(temp);
			}
			
		}
		
		
		fos.flush();
		fos.close();
		re.close();
		
		ps.close();
		con.close();
}
}