java连数据库_jdbc1

<pre name="code" class="java"><pre code_snippet_id="385907" snippet_file_name="blog_20140610_1_1241574" name="code" class="java">

public class Constants
{
	// 定义mysql的驱动
	public static final String driver = "org.gjt.mm.mysql.Driver";

	// 定义mysql数据库连接的地址
	public static final String url = "jdbc:mysql://localhost:3306/db_servlet";

	// 定义mysql数据库的连接用户名
	public static final String user = "root";

	// 定义mysql数据的连接密码
	public static final String password = "root";
}

__________________________________________________________________________________________________________________________________________________________

</pre><pre code_snippet_id="385907" snippet_file_name="blog_20140610_4_9165626" name="code" class="java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcTemplate {
	private Connection conn = null;

	private Statement stmt = null;

	private ResultSet rs = null;

	private PreparedStatement prestmt = null;

	static {
		try {
			Class.forName(Constants.driver).newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 获取连接
	private void getConnection() {
		try {
			conn = DriverManager.getConnection(Constants.url, Constants.user,
					Constants.password);
			conn.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 创建statement
	private void createStatement() {

		try {
			this.getConnection();
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 创建preparedstatement
	private void createPreparedStatement(String sql) {

		try {
			this.getConnection();
			prestmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 执行查询的方法
	 * 
	 * @param sql
	 *            查询sql语句
	 * @return rs
	 */
	public ResultSet executeQuery(String sql) {
		this.createStatement();

		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 执行增删改的方法
	 * 
	 * @param sql
	 *            曾删改的sql
	 * @return 影响的行数
	 */
	public int executeUpdate(String sql) {
		int rowCount = -1;

		this.createStatement();

		try {
			rowCount = stmt.executeUpdate(sql);

			this.runCommit();
		} catch (SQLException e) {
			this.rollBack();
			e.printStackTrace();
		} finally {
			// 关闭资源
			this.closeResource();
		}
		return rowCount;
	}

	/**
	 * 执行增删改的方法
	 * 
	 * @param sql
	 *            带？号的查询sql语句
	 * @param args
	 *            给问号赋值的String【】
	 * @return
	 */
	public ResultSet executeQuery(String sql, String[] args) {
		this.createPreparedStatement(sql);

		try {
			this.operateParameter(args);

			rs = prestmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 执行增删改的方法
	 * 
	 * @param sql
	 *            带？号的增删改sql语句
	 * @param args
	 *            给？号赋值的string【】
	 * @return 影响的行数
	 */
	public int executeUpdate(String sql, String[] args) {
		int rowCount = -1;

		this.createPreparedStatement(sql);

		try {
			this.operateParameter(args);

			rowCount = prestmt.executeUpdate();

			this.runCommit();
		} catch (SQLException e) {
			this.rollBack();
			e.printStackTrace();
		} finally {
			this.closeResource();
		}
		return rowCount;

	}

	/**
	 * 执行批处理的方法
	 * 
	 * @param sqls
	 *            曾删改的多条sql
	 * @return 总共的影响行数
	 */
	public int executeBatch(String[] sqls) {
		int total = 0;

		this.createStatement();

		try {
			for (int i = 0; sqls != null && i < sqls.length; i++) {
				stmt.addBatch(sqls[i]);
			}
			total = stmt.executeBatch().length;

			this.runCommit();

		} catch (SQLException e) {
			this.rollBack();
			e.printStackTrace();
		} finally {
			this.closeResource();
		}

		return total;

	}

	public String executeScalar(String sql) {
		String result = "";

		this.createStatement();

		try {
			rs = stmt.executeQuery(sql);

			if (rs.next()) {
				result = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.closeResource();
		}
		return result;
	}

	// 给？号赋值的方法
	private void operateParameter(String[] args) throws SQLException {
		if (args != null && args.length > 0) {
			for (int i = 0; i < args.length; i++) {
				prestmt.setString(i + 1, args[i]);
			}
		}
	}

	// 关闭资源的方法
	public void closeResource() {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
			if (prestmt != null) {
				prestmt.close();
				prestmt = null;
			}
			if (conn != null) {
				conn.close();
				conn = null;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 提交的方法
	private void runCommit() {
		try {
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 回滚的方法
	private void rollBack() {
		try {
			conn.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}