java数据库编程

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