1. Demo1
导包时 不要导具体的mysql包, 为了兼容性,导JDBC 中 sql的包既可以了。
public class Demo1 { /** * @param args * @throws ClassNotFoundException * @throws SQLException * @throws Exception */ public static void main(String[] args) throws ClassNotFoundException, SQLException { String url = "jdbc:mysql://localhost:3306/day14"; String username = "root"; String password = "root"; Connection conn = null; Statement st = null; ResultSet rs = null; try{ //1.加载驱动(开发推荐的方式) Class.forName("com.mysql.jdbc.Driver"); //2.获取与数据库的链接,链接资源有限,尽量晚的创建,尽量早的释放 conn = (com.mysql.jdbc.Connection) DriverManager.getConnection(url, username, password); System.out.println(conn); //3.获取用于向数据库发送sql语句的statement st = conn.createStatement(); //4.向数据库发sql,并获取代表结果集的resultset String sql = "select id,name,password,email,birthday from users"; rs = st.executeQuery(sql); //5.取出结果集的数据 rs.afterLast(); rs.previous(); System.out.println("id=" + rs.getObject("id")); System.out.println("name=" + rs.getObject("name")); System.out.println("password=" + rs.getObject("password")); System.out.println("email=" + rs.getObject("email")); System.out.println("birthday=" + rs.getObject("birthday")); }finally{ //6.关闭链接,释放资源 if(rs!=null){ try{ rs.close(); }catch (Exception e) { e.printStackTrace(); } rs = null; } if(st!=null){ try{ st.close(); }catch (Exception e) { e.printStackTrace(); } } if(conn!=null){ try{ conn.close(); }catch (Exception e) { e.printStackTrace(); } } } } }
ResultSet还提供了对结果集进行滚动的方法:
•next():移动到下一行
•Previous():移动到前一行
•absolute(int row):移动到指定行
•beforeFirst():移动resultSet的最前面。
•afterLast() :移动到resultSet的最后面。
2. 增、删、改、查 示例
//使用jdbc对数据库增删改查 public class Demo4 { @Test public void insert(){ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "insert into users(id,name,password,email,birthday) values(4,'xxx','123','xx@sina.com',to_date('1980-09-09','YYYY-MM-DD'))"; int num = st.executeUpdate(sql); //update if(num>0){ System.out.println("插入成功!!"); } }catch (Exception e) { e.printStackTrace(); }finally{ JdbcUtils.release(conn, st, rs); } } @Test public void delete(){ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "delete from users where id=4"; st = conn.createStatement(); int num = st.executeUpdate(sql); if(num>0){ System.out.println("删除成功!!"); } }catch (Exception e) { }finally{ JdbcUtils.release(conn, st, rs); } } @Test public void update(){ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "update users set name='wuwang',email='wuwang@sina.com' where id=3"; st = conn.createStatement(); int num = st.executeUpdate(sql); if(num>0){ System.out.println("更新成功!!"); } }catch (Exception e) { }finally{ JdbcUtils.release(conn, st, rs); } } @Test public void find(){ Connection conn = null; Statement st = null; ResultSet rs = null; try{ conn = JdbcUtils.getConnection(); String sql = "select * from users where id=1"; st = conn.createStatement(); rs = st.executeQuery(sql); if(rs.next()){ System.out.println(rs.getString("name")); } }catch (Exception e) { }finally{ JdbcUtils.release(conn, st, rs); } } }工具类
public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static{ try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties prop = new Properties(); prop.load(in); driver = prop.getProperty("driver"); url = prop.getProperty("url"); username = prop.getProperty("username"); password = prop.getProperty("password"); Class.forName(driver); }catch (Exception e) { throw new ExceptionInInitializerError(e); } } public static Connection getConnection() throws SQLException{ return DriverManager.getConnection(url, username,password); } public static void release(Connection conn,Statement st,ResultSet rs){ if(rs!=null){ try{ rs.close(); }catch (Exception e) { e.printStackTrace(); } rs = null; } if(st!=null){ try{ st.close(); }catch (Exception e) { e.printStackTrace(); } } if(conn!=null){ try{ conn.close(); }catch (Exception e) { e.printStackTrace(); } } } }配置文件 db.properties , 配置文件 每行后面不要有空格,propertie解析的时候会将空格也解析。
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/day14 username=root password=root #driver=oracle.jdbc.driver.OracleDriver #url=jdbc:oracle:thin:@localhost:1521:orcl #username=root #password=root
2. JavaWeb 工程 结构图