java操作数据库

1.操作DML

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDML {

	public static void main(String[] args) {
		// Statement stmt = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
			// stmt = conn.createStatement();
			// String sql = "insert into dept values (60, 'F', 'F')";
			// stmt.executeUpdate(sql);
			pstmt = conn.prepareStatement("insert into dept values (?, ?, ?)");
			pstmt.setInt(1, 70);
			pstmt.setString(2, "G");
			pstmt.setString(3, "G");
			pstmt.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				// if (stmt != null) {
				// stmt.close();
				// stmt = null;
				// }
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 

2.调用存储过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class TestProc {

	public static void main(String[] args) {
		CallableStatement cstmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
			// p (v_a in number, v_b number, v_c out number, v_d in out number)
			cstmt = conn.prepareCall("(call p(?, ?, ?, ?))");
			// 指定第三个和第四个是输出参数
			cstmt.registerOutParameter(3, Types.INTEGER);
			cstmt.registerOutParameter(4, Types.INTEGER);
			// 给输入参数赋值
			cstmt.setInt(1, 30);
			cstmt.setInt(2, 40);
			cstmt.setInt(4, 50);
			cstmt.execute();
			System.out.println(cstmt.getInt(3) + "	" + cstmt.getInt(4));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (cstmt != null) {
					cstmt.close();
					cstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

 

3.batch批处理

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * 这里面做了事务的管理，共五步骤[不要求代码严谨的话，可以不要这五步骤]
 * 
 */
public class TestBatch {

	public static void main(String[] args) {
		// Statement stmt = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");

			conn.setAutoCommit(false);// 1.设置不自动提交

			// stmt = conn.createStatement();
			// stmt.addBatch("insert into dept values (80, 'H', 'H')");
			// stmt.addBatch("insert into dept values (90, 'I', 'I')");
			// stmt.addBatch("insert into dept values (100, 'J', 'J')");
			// stmt.executeBatch();

			pstmt = conn.prepareStatement("insert into dept values (?, ?, ?)");

			pstmt.setInt(1, 110);
			pstmt.setString(2, "K");
			pstmt.setString(3, "K");
			pstmt.addBatch();

			pstmt.setInt(1, 120);
			pstmt.setString(2, "L");
			pstmt.setString(3, "L");
			pstmt.addBatch();

			pstmt.setInt(1, 130);
			pstmt.setString(2, "M");
			pstmt.setString(3, "M");
			pstmt.addBatch();

			pstmt.executeBatch();

			conn.commit();// 2.手动提交
			conn.setAutoCommit(true);// 3.回复自动提交
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				if (conn != null) {
					conn.rollback();// 4.回滚事务
					conn.setAutoCommit(true);// 5.回复自动提交
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				// if (stmt != null) {
				// stmt.close();
				// stmt = null;
				// }
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

 

4.可滚动的结果集

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestScroll {

	public static void main(String[] args) {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
			// 可滚动的结果集[对于滚动不敏感，并且只读]
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery("select * from dept");
			rs.next();// 结果集向后移一位
			System.out.println(rs.getString("dname") + "	" + rs.getInt("deptno"));
			rs.last();// 结果集定位到最后一位
			System.out.println(rs.getString("dname") + "	" + rs.getInt("deptno"));
			System.out.println(rs.isLast());// 是否最后一条
			System.out.println(rs.isAfterLast());// 是否最后的下一条
			System.out.println(rs.getRow());// 当前在哪个位置
			rs.previous();// 结果集向前移一位
			System.out.println(rs.getString("dname") + "	" + rs.getInt("deptno"));
			rs.absolute(6);// 定位结果集到第六位
			System.out.println(rs.getString("dname") + "	" + rs.getInt("deptno"));

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

 

5.可更新的结果集

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * mysql支持可更新的结果集，而oracle不支持
 * 
 */
public class TestUpdateResultSet {

	public static void main(String[] args) {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = stmt.executeQuery("select * from dept");

			rs.next();
			// 更新一条数据
			rs.updateString("dname", "AAAAA");
			rs.updateRow();

			// 插入一条数
			rs.moveToInsertRow();
			rs.updateInt("deptno", 140);
			rs.updateString("dname", "N");
			rs.updateString("loc", "N");
			rs.insertRow();

			// 将光标移到新建的行
			rs.moveToCurrentRow();

			// 删除第六行
			rs.absolute(6);
			rs.deleteRow();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}