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 blocke.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 blocke1.printStackTrace();}//进行回滚操作} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blockSystem.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 blocke.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();}}