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中访问数据库的步骤

 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 }

扫码关注我们

微信号：SRE实战

拒绝背锅 运筹帷幄

×

选择打赏方式：

微信 QQ钱包 支付宝

打赏

打赏

打赏

多少都是心意！谢谢大家！！！