JDBC示例程序
程序员文章站
2022-03-30 18:01:39
DAO Java中访问数据库的步骤 数据库的基本连接 ......
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 }