package senssic.demo;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
public class DBClass {
public static void main(String[] args) throws Exception {
DBInfo dInfo = new DBInfo();
dInfo.getInfoP();
DBBOperate dOperate = new DBBOperate();
DBLob dLob = new DBLob();
DBTr dTr = new DBTr();
}
}
/**
* 数据库的基本操作
*
* @author Administrator
*
*/
class DBBOperate {
PreparedStatement ps = null;
Connection connection = null;
private final String initsql = "drop table dbtest";
private final String creatsql = "create table dbtest(num int primary key AUTO_INCREMENT,user varchar(35),pssd varchar(35))";
private final String insertsql = "insert into dbtest(user,pssd) values('senssic','chizhou')";
private final String deletesql = "delete from dbtest where num=1";
private final String updatesql = "update dbtest set user='qiyu'where num=2";
private final String selectsql = "select * from dbtest";
public DBBOperate() {
exec(initsql);
exec(creatsql);
exec(insertsql);
exec(insertsql);
query(selectsql);
exec(updatesql);
query(selectsql);
exec(deletesql);
query(selectsql);
DBUtil.close(ps, connection);
}
public void exec(String sql) {
try {
connection = DBUtil.getConnection();
ps = connection.prepareStatement(sql);
ps.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void query(String sql) {
ResultSet rSet = null;
try {
connection = DBUtil.getConnection();
ps = connection.prepareStatement(sql);
rSet = ps.executeQuery();
while (rSet.next()) {
System.out.println("编号:" + rSet.getInt(1) + "姓名:"
+ rSet.getString(2) + "密码:" + rSet.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 数据库的大数据操作,最大4g
*
* @author Administrator
*
*/
class DBLob {
private PreparedStatement pStatement = null;
private Connection connection = null;
private final String initsql = "drop table dblob";
private final String createsql = "create table dblob(num int primary key AUTO_INCREMENT,note longtext,video longblob)";
private final String insertb = "insert into dblob(note,video) value(?,?)";
private final String selectsql = "select * from dblob";
public DBLob() {
try {
DBUtil.getConnection().prepareStatement(initsql).execute();
DBUtil.getConnection().prepareStatement(createsql).execute();
} catch (SQLException e) {
e.printStackTrace();
}
execinsert(insertb);
execget(selectsql);
}
// 保存
public void execinsert(String sql) {
try {
pStatement = DBUtil.getConnection().prepareStatement(sql);
File f = new File("d:" + File.separator + "a.txt");
File f2 = new File("d:" + File.separator + "b.wmv");
InputStream fStream = new FileInputStream(f);
InputStream fStream2 = new FileInputStream(f2);
pStatement.setAsciiStream(1, fStream, (int) f.length());// 必须为int,因为length()方法返回long需要转换,因为jdk6的jdbc数据驱动还没实现long
pStatement.setBinaryStream(2, fStream2, (int) f2.length());
pStatement.execute();
} catch (Exception e) {
// 如果报错:Incorrect string value:
// '\xA1\xA1\xA1\xA1\x0D\x0A.需要查看数据库编码表编码改成utf8
e.printStackTrace();
}
}
//提取
public void execget(String sql) {
connection = DBUtil.getConnection();
try {
pStatement = connection.prepareStatement(selectsql);
ResultSet rSet = pStatement.executeQuery();
while (rSet.next()) {
FileInputStream fStream = (FileInputStream) rSet
.getAsciiStream("note");
FileInputStream fStream2 = (FileInputStream) rSet
.getBinaryStream("video");
byte[] byt = new byte[1024];
FileOutputStream fout = new FileOutputStream("d:"
+ File.separator + new Date() + ".doc");
FileOutputStream fout2 = new FileOutputStream("d:"
+ File.separator + new Date() + ".wmv");
while (fStream2.read(byt) != -1) {
fout2.write(byt);
}
fout2.close();
fStream2.close();
fout.close();
fStream.close();
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
/**
* 事务处理
*/
class DBTr {
private final static String url = "jdbc:mysql://localhost:3306/DB";
private final static String usr = "root";
private final static String pws = "qiyu0126";
private Connection connection = null;
private CallableStatement cStatement = null;
public DBTr() {
try {
Class.forName("org.gjt.mm.mysql.Driver");
connection = DriverManager.getConnection(url, usr, pws);
connection.setAutoCommit(false);// 设置自动提交为false
connection
.prepareStatement("insert into dbtest(user,pssd) values('china','anhui')");
connection.setSavepoint();// 设置保存点,用于以后发生错误回滚
cStatement = connection.prepareCall("{call procname(?,?)}");// 调用存储过程
cStatement.setInt(1, 20);
cStatement.setString(2, "senssic");// 设置输入信息
cStatement.registerOutParameter(2, Types.VARCHAR);// 注册输出信息
cStatement.execute();
connection.commit();// 提交所有事务
System.out.println(cStatement.getString(2));// 输出返回值
connection.close();
cStatement.close();
} catch (Exception e) {
try {// 一旦出错回滚到保存点(没有保存点则全部回滚)
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
/**
* 获取数据库的一些基本信息
*/
class DBInfo {
private DatabaseMetaData dmd = null;
public DBInfo() {
try {
dmd = DBUtil.getConnection().getMetaData();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void getInfoP() throws Exception {
System.out.println("数据库名称:" + dmd.getDatabaseProductName() + "\n\t版本号:"
+ dmd.getDatabaseProductVersion());
System.out.println("jdbc驱动名称:" + dmd.getDriverName() + "\n\tjdbc驱动版本号:"
+ dmd.getDriverVersion());
System.out.println("最大列长度:" + dmd.getMaxColumnNameLength()
+ "\n表中允许最大列个数:" + dmd.getMaxColumnsInTable()
+ "\n允许最大的并发连接数(0意味着没有限制或限制是未知的):" + dmd.getMaxConnections()
+ "\n此数据库的数学函数:" + dmd.getNumericFunctions());
ResultSet resultSet = dmd.getTypeInfo();
System.out.println("数据库中的数据类型:");
while (resultSet.next()) {
System.out.print(resultSet.getString("TYPE_NAME") + "\t");
}
}
}
class DBUtil {
private final static String url = "jdbc:mysql://localhost:3306/DB";
private final static String usr = "root";
private final static String pws = "qiyu0126";
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName("org.gjt.mm.mysql.Driver");
connection = DriverManager.getConnection(url, usr, pws);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
public static void close(PreparedStatement pStatement, Connection connection) {
try {
if (pStatement != null || pStatement != null) {
pStatement.close();
connection.close();
}
} catch (Exception e) {
// TODO: handle exception
}
}
}
结果(部分):
数据库名称:MySQL
版本号:6.0.2-alpha-community-nt-debug
jdbc驱动名称:MySQL-AB JDBC Driver
jdbc驱动版本号:mysql-connector-java-3.1.13 ( $Date: 2005-11-17 15:53:48 +0100 (Thu, 17 Nov 2005) $, $Revision$ )
最大列长度:64
表中允许最大列个数:512
允许最大的并发连接数(0意味着没有限制或限制是未知的):0
此数据库的数学函数:ABS,ACOS,ASIN,ATAN,ATAN2,BIT_COUNT,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,MAX,MIN,MOD,PI,POW,POWER,RADIANS,RAND,ROUND,SIN,SQRT,TAN,TRUNCATE
数据库中的数据类型:
BIT BOOL TINYINT BIGINTLONG VARBINARY MEDIUMBLOBLONGBLOB BLOBTINYBLOB VARBINARYBINARY LONG VARCHARMEDIUMTEXT LONGTEXTTEXT TINYTEXTCHAR NUMERICDECIMAL INTEGERINT MEDIUMINTSMALLINT FLOATDOUBLE DOUBLE PRECISIONREAL VARCHARENUM SETDATE TIMEDATETIME TIMESTAMP编号:1姓名:senssic密码:chizhou
编号:2姓名:senssic密码:chizhou
编号:1姓名:senssic密码:chizhou
编号:2姓名:qiyu密码:chizhou
编号:2姓名:qiyu密码:chizhou