Java JDBC

最新推荐文章于 2022-11-16 11:56:03 发布

High_Gent

最新推荐文章于 2022-11-16 11:56:03 发布

阅读量244

点赞数

文章标签： jdbc

本文链接：https://blog.csdn.net/qq_29593573/article/details/76034747

版权

JDBC 基础学习总结

java数据库包 java.sql.*
JDBC主要过程
- 加载驱动：Class.forName(“com.mysql.jdbc.Driver”);
- 获得数据库连接：DriverManager常用其getConnection(String url,String username,String password)方法进行数据库连接；
- 创建Statement 或PreparedStatement：用于向数据库发送需要执行的sql语句获得结果集；
- 执行sql语句：得到ResultSet结果集；
- 解析返回结果集；
- 关闭连接；

Statement接口(多用于不带参执行sql语句)

得到Statement实例：Connection的createStatement();
常用方法；
- boolean excute(String sql)：执行给定sql语句，用于不返回结果；
- ResultSet excuteQuery(String sql)：执行给定sql语句，用于返回结果；
- int excuteUpdate(String sql)：执行给定 SQL 语句，该语句可能为 INSERT、UPDATE 或 DELETE 语句成功的条数

PreparedStatement接口继承自Statement（多用于带参数查询，其有自动校验格式问题）

得到PreparedStatement实例：Connection的prepareSatement(String sql)方法；
常用方法：
- setString(int parameterIndex, String x)：将指定参数设定为指定字符串；
- setTimestamp(int parameterIndex, Timestamp x)：该方法中常用到生成Timestamp：new java.sql.Timestamp(Date.getTime())；
- boolean excute()：常用来执行setxxx语句；
- ResultSet executeQuery()：用来得到结果集；
- setObject(int parameterIndex, Object x)：自动将给定参数将被转换为相应 SQL 类型

ResultSet接口

常用方法：
- next()：将光标从当前位置向前移一行；用来解析时判断是否还有数据；
- getxxx()：常用来给对应Dao中对应类属性赋值；

PreparedStatement的增、删、改

通常将数据库的url、user（用户名）、password设为常量；

private static final String CONN_URL = "jdbc:mysql://localhost:3306/需连接数据库名?useUnicode=true&characterEncoding=utf8";
private static final String USER = "root";
private static final String PASSWORD = "root";
public static void insert(String username, String password, Date regDate) {
	Connection conn = null;

	try {
		Class.forName("com.mysql.jdbc.Driver");

		conn = DriverManager.getConnection(CONN_URL, USER, PASSWORD);

		String sql = "insert into user(username,passwd,reg_time)values(?,?,?)";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, username);
		ps.setString(2, password);
		ps.setTimestamp(3, new java.sql.Timestamp(regDate.getTime()));

		ps.execute();
		System.out.println("执行成功");

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

}
	

public static void delete(String username) {
	Connection conn = null;
	
	try {
		Class.forName("com.mysql.jdbc.Driver");
		
		conn = DriverManager.getConnection(CONN_URL, USER, PASSWORD);
		
		String sql = "delete from user where username=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, username);
		ps.execute();
		System.out.println("执行成功");
		
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}


public static void update(int id,String password) {
	Connection conn = null;
	
	try {
		Class.forName("com.mysql.jdbc.Driver");
		
		conn = DriverManager.getConnection(CONN_URL, USER, PASSWORD);
		
		String sql = "update user set passwd=? where user_id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, password);
		ps.setInt(2, id);
		int count = ps.executeUpdate();
		System.out.println("执行成功,共影响了"+count+"条");
		
	} catch (ClassNotFoundException e) {
		e.printStackTrace();
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
}

JDBCHelper

public class DBHelper {

	private Connection connection;
	private List<AutoCloseable> closeableList= new ArrayList<>();

	/**
	 * 得到链接
	 * 
	 * @return
	 * @throws SQLException 
	 * @throws FileNotFoundException 
	 */
	public Connection getConn() throws SQLException{
		try {
			if (connection==null) {
				/*Class.forName("com.mysql.jdbc.Driver");
				//properties文本的到相应url、user、password
				Properties prop = new Properties();
				String path = this.getClass().getClassLoader().getResource("mysql.properties").getPath();
				FileInputStream fis = new FileInputStream(path);
				prop.load(fis);
				
				String url = prop.getProperty("conn_url");
				String user = prop.getProperty("user");
				String password = prop.getProperty("password");*/
				Properties prop = new Properties();
                System.out.println( this.getClass().getClassLoader().getResource(""));
                System.out.println( this.getClass().getClassLoader().getResource("config/sxevn.properties").getPath());
                InputStream fis = this.getClass().getResourceAsStream("/config/sxevn.properties");
                prop.load(fis);
                Class.forName(prop.getProperty("jdbc.driverClassName"));
                String url = prop.getProperty("jdbc.url");
                String user = prop.getProperty("jdbc.username");
                String password = prop.getProperty("jdbc.password");
				connection = DriverManager.getConnection(url, user, password);
				closeableList.add(connection);
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return connection;
	}
	/**
	 * 开启事务
	 * @throws SQLException 
	 */
	public void startTransaction() throws SQLException{
		
		getConn();
		connection.setAutoCommit(false);
		
	}
	/**
	 * 回滚
	 * @throws SQLException 
	 */
	public void rollback() throws SQLException{
		connection.rollback();
		connection.setAutoCommit(true);
	}
	/**
	 * 提交
	 * @throws SQLException 
	 */
	public void commit() throws SQLException{
		connection.commit();
		connection.setAutoCommit(true);
	}
	/**
	 * 执行查询
	 * 
	 * @param sql
	 *            预编译的sql，带？占位
	 * @param params
	 *            sql中需要代入的参数
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql, Object[] params) throws SQLException{
		
		getConn();
		ResultSet rs = null;
		PreparedStatement ps=null;
		
		ps = connection.prepareStatement(sql);
		closeableList.add(ps);
		for (int i = 1; i <= params.length; i++) {
			ps.setObject(i, params[i - 1]);
		}
		rs = ps.executeQuery();
		closeableList.add(rs);
		
		return rs;
	}
	/*public ResultSet executeQuery(String sql) throws SQLException{
		
		getConn();
		ResultSet resultSet = null;
		Statement statement = connection.createStatement();
		resultSet = statement.executeQuery(sql);
		return resultSet;
	}*/

	public ResultSet executeQuery(String sql) throws SQLException{
		ResultSet rs = null;
		PreparedStatement ps=null;
		getConn();
		try {
			ps = connection.prepareStatement(sql);
			closeableList.add(ps);	
			rs = ps.executeQuery();
			closeableList.add(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	/**
	 * 执行insert
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException 
	 */
	public boolean execute(String sql, Object[] params) throws SQLException{
		boolean rs = false;
		PreparedStatement ps=null;
		getConn();
			ps = connection.prepareStatement(sql);
			closeableList.add(ps);
			
			for (int i = 1; i <= params.length; i++) {
				ps.setObject(i, params[i - 1]);
			}
			
			ps.execute();
			rs=true;
		
		return rs;
	}

	/**
	 * 执行更新(插入)
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public int executeUpdate(String sql,Object[] params) throws SQLException{
		
		getConn();
		PreparedStatement ps= connection.prepareStatement(sql);
		closeableList.add(ps);
		for (int i = 1; i <= params.length; i++) {
			ps.setObject(i, params[i - 1]);
		}
			
		return ps.executeUpdate();
	}
	
	
	/**
	 * 清理资源
	 */
	public void cleanup() {
		try {
			connection.setAutoCommit(true);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		//将资源倒序
		Collections.reverse(closeableList);
		//挨个清理
		for(AutoCloseable ac : closeableList){
			//System.out.println(ac);
			if(ac!=null){
				try {
					ac.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		closeableList.clear();
	}

}

#conn_url=jdbc:mysql://localhost:1433/sx_test?useUnicode=true&characterEncoding=utf8
#user=zhangs
#password=123456

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:1433/sx_test?useUnicode=true&characterEncoding=utf8
jdbc.username=zhangs
jdbc.password=123456