JDBC示例程序
1 import java.io.IOException; 2 import java.io.InputStream; 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 import java.util.Properties; 9 10 import org.apache.commons.dbcp.BasicDataSource; 11 12 public class DBUtils { 13 private static BasicDataSource dateSource; 14 static { 15 //创建属性对象 16 Properties prop = new Properties(); 17 //得到文件的输入流 18 InputStream ips = DBUtils2.class.getClassLoader().getResourceAsStream("jdbc.properties"); 19 //把文件加载到属性对象中 20 try { 21 prop.load(ips); 22 //读取数据 23 String driver = prop.getProperty("driver"); 24 String url = prop.getProperty("url"); 25 String username = prop.getProperty("username"); 26 String password = prop.getProperty("password"); 27 //创建数据源对象 28 dateSource = new BasicDataSource(); 29 //设置数据库链接信息 30 dateSource.setDriverClassName(driver); 31 dateSource.setUrl(url); 32 dateSource.setUsername(username); 33 dateSource.setPassword(password); 34 //设置连接池参数 35 dateSource.setInitialSize(3);//初始连接数量 36 dateSource.setMaxActive(5);//最大连接数量 37 38 } catch (IOException e) { 39 e.printStackTrace(); 40 } 41 42 } 43 //1、获取链接 44 public static Connection getConn() throws Exception { 45 46 47 //获取连接池中的连接 48 Connection conn = dateSource.getConnection(); 49 return conn; 50 } 51 //2、关闭资源 52 public static void close(ResultSet rs,Statement stat,Connection conn) { 53 try { 54 if (rs!=null) { 55 rs.close(); 56 } 57 } catch (SQLException e) { 58 e.printStackTrace(); 59 } 60 try { 61 if (stat!=null) { 62 stat.close(); 63 } 64 } catch (SQLException e) { 65 e.printStackTrace(); 66 } 67 //关闭连接 68 try { 69 if (conn!=null) { 70 conn.close(); 71 } 72 } catch (SQLException e) { 73 e.printStackTrace(); 74 } 75 } 76 }
DAO
1 /** 2 * 依据用户名查询对应的用户信息。 如果找不到,返回null。 3 * 4 * @throws SQLException 5 */ 6 public User find(String uname) throws SQLException { 7 User user = null; 8 9 Connection conn = null; 10 PreparedStatement ps = null; 11 ResultSet rs = null; 12 13 try { 14 conn = DBUtils.getconn(); 15 String sql = "SELECT * FROM t_user " + "WHERE username=?"; 16 ps = conn.prepareStatement(sql); 17 ps.setString(1, uname); 18 rs = ps.executeQuery(); 19 20 if (rs.next()) { 21 int id = rs.getInt("id"); 22 String pwd = rs.getString("password"); 23 String email = rs.getString("email"); 24 25 user = new User(); 26 user.setId(id); 27 user.setUname(uname); 28 user.setPwd(pwd); 29 user.setEmail(email); 30 31 } 32 33 } catch (SQLException e) { 34 e.printStackTrace(); 35 throw e; 36 } finally { 37 DBUtils.close(rs, ps, conn); 38 } 39 40 return user; 41 } 42 /** 43 * 删除指定信息 44 * @param id 45 * @throws SQLException 46 */ 47 public void delete(int id) throws SQLException { 48 Connection conn = null; 49 PreparedStatement ps = null; 50 51 try { 52 conn = DBUtils.getconn(); 53 String sql = "DELETE FROM t_user " + "WHERE id = ?"; 54 ps = conn.prepareStatement(sql); 55 ps.setInt(1, id); 56 ps.executeUpdate(); 57 58 } catch (SQLException e) { 59 e.printStackTrace(); 60 throw e; 61 } finally { 62 DBUtils.close(null, ps, conn); 63 } 64 } 65 66 /** 67 * 将用户信息插入到t_user表。 68 * 69 * @throws SQLException 70 * 71 */ 72 public void save(User user) throws SQLException { 73 Connection conn = null; 74 PreparedStatement ps = null; 75 76 try { 77 conn = DBUtils.getconn(); 78 String sql = "INSERT INTO t_user " + "VALUES(null,?,?,?)"; 79 ps = conn.prepareStatement(sql); 80 ps.setString(1, user.getUname()); 81 ps.setString(2, user.getPwd()); 82 ps.setString(3, user.getEmail()); 83 ps.executeUpdate(); 84 85 } catch (SQLException e) { 86 e.printStackTrace(); 87 throw e; 88 } finally { 89 DBUtils.close(null, ps, conn); 90 } 91 } 92 93 /** 94 * 从t_user表中查询出所有用户的信息。 注: 一条记录对应一个User对象(即将记录中的数据 存放到User对象里面)。 95 * 96 * @throws SQLException 97 */ 98 public List<User> findAll() throws SQLException { 99 100 List<User> users = new ArrayList<User>(); 101 102 Connection conn = null; 103 PreparedStatement ps = null; 104 ResultSet rs = null; 105 106 try { 107 conn = DBUtils.getconn(); 108 String sql = "SELECT * FROM t_user"; 109 ps = conn.prepareStatement(sql); 110 rs = ps.executeQuery(); 111 112 while (rs.next()) { 113 114 int id = rs.getInt("id"); 115 String uname = rs.getString("username"); 116 String pwd = rs.getString("password"); 117 String email = rs.getString("email"); 118 119 User user = new User(); 120 user.setId(id); 121 user.setUname(uname); 122 user.setPwd(pwd); 123 user.setEmail(email); 124 125 users.add(user); 126 127 } 128 129 } catch (SQLException e) { 130 e.printStackTrace(); 131 throw e; 132 } finally { 133 DBUtils.close(rs, ps, conn); 134 } 135 136 return users; 137 138 }
Java中访问数据库的步骤
SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。 1 //注册驱动 2 Class.forName("com.mysql.jdbc.Driver"); 3 //建立连接 4 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", ""); 5 System.out.println("创建完毕"); 6 //创建Statement 7 Statement stat = conn.createStatement(); 8 String sql = "delete from jdbc01 where id=1"; 9 //执行sql语句(若SQL语句为查询语句需要处理结果集) 10 stat.executeUpdate(sql); 11 System.out.println("删除完毕"); 12 //关闭连接 13 stat.close(); 14 conn.close();
数据库的基本连接
1 public static void main(String[] args) throws Exception { 2 //创建数据源对象 3 BasicDataSource dateSource = new BasicDataSource(); 4 //设置数据库连接信息 5 dateSource.setDriverClassName("com.mysql.jdbc.Driver"); 6 dateSource.setUrl("jdbc:mysql://localhost:3306/db3"); 7 dateSource.setUsername("root"); 8 dateSource.setPassword("root"); 9 //设置连接池参数 10 dateSource.setInitialSize(3);//初始连接数量 11 dateSource.setMaxActive(5);//最大连接数量 12 //获取连接池中的连接 13 Connection conn = dateSource.getConnection(); 14 System.out.println(conn); 15 }
更多精彩