本文共 8052 字，大约阅读时间需要 26 分钟。

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
   
     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=123456jdbc.driverClassName=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://localhost:1433/sx_test?useUnicode=true&characterEncoding=utf8jdbc.username=zhangsjdbc.password=123456

转载地址：https://blog.csdn.net/qq_29593573/article/details/76034747 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！