数据库_jdbc_入门

demo1

package cn.itcast.demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/*mysql -uroot -prootset character_set_client=gb2312;set character_set_results=gb2312;create database day14;use day14;show tables; create table user( id int primary key, name varchar(20), psw varchar(40), email varchar(40), birthday date ); insert into user(id,name,psw,email,birthday) values(1,'林黛玉','lindaiyu','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(2,'薛宝钗','xuebaochai','[e-mail]','1993-06-07'); insert into user(id,name,psw,email,birthday) values(3,'史湘云','shixiangyun','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(4,'妙玉','miaoyu','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(5,'晴雯','qinwen','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(6,'爱哥哥','igg','[e-mail]','1991-06-07'); select * from user; */public class demo1 {  public static void main(String[] args) throws SQLException {    /*方法说明：     *jdbc连接标准6步！     */    //1加载驱动    DriverManager.registerDriver(new com.mysql.jdbc.Driver());    //2获取Connection    String url="jdbc:mysql://localhost:3306/day14";    String user="root";    String password="root";    Connection conn=DriverManager.getConnection(url, user, password);    //3获取Statement对象,专门用于发送SQL语句    Statement st=conn.createStatement();    //4用Statement向数据库执行查询语句,返回结果集ResultSet    String sql="select * from user";    ResultSet rs=st.executeQuery(sql);    //ResultSet rs = st.executeQuery("select * from user");    //5while循环结果集指针    while (rs.next()) {      System.out.println("id="+rs.getObject("id"));      System.out.println("name="+rs.getObject("name"));      System.out.println("psw="+rs.getObject("psw"));      System.out.println("email="+rs.getObject("email"));      System.out.println("birthday="+rs.getObject("birthday"));    }    //6关闭资源,先ResultSet再Statement最后Connection    rs.close();    st.close();    conn.close();  }}

demo2_1

package cn.itcast.demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import cn.itcast.domain.User;public class demo2_1 {  public static void main(String[] args) throws SQLException, ClassNotFoundException {    /*方法说明：     *正宗的jdbc连接标准6步！ */    String url="jdbc:mysql://localhost:3306/day14?useUnicode=true&characterEncoding=utf-8";    String user="root";    String password="root";    Connection conn=null;    Statement st=null;    ResultSet rs=null;    List list=null;    try {      //1加载驱动      Class.forName("com.mysql.jdbc.Driver");      //2获取Connection      conn=DriverManager.getConnection(url, user, password);      //3获取Statement对象,专门用于发送SQL语句      st=conn.createStatement();      //4用Statement向数据库执行查询语句,返回结果集ResultSet      rs = st.executeQuery("select * from user");      //5while循环结果集指针      list=new ArrayList();      while (rs.next()) {        //一般是将获取的数据封闭到JavaBean        User user1=new User();        user1.setId(rs.getInt("id"));        user1.setName(rs.getString("name"));        user1.setPsw(rs.getString("psw"));        user1.setEmail(rs.getString("email"));        user1.setBirthday(rs.getDate("birthday"));        list.add(user1);      }      System.out.println(list);    } finally{      //6关闭资源,先ResultSet再Statement最后Connection      if (rs!=null) {        try {          rs.close();        } catch (Exception e) {          e.printStackTrace();        }        rs=null;      }      if (st!=null) {        try {          st.close();        } catch (Exception e) {          e.printStackTrace();        }        st=null;      }      if (conn!=null) {        try {          conn.close();        } catch (Exception e) {          e.printStackTrace();        }        conn=null;      }    }  }}

demo2

package cn.itcast.demo;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import cn.itcast.domain.User;/*mysql -uroot -prootset character_set_client=gb2312;set character_set_results=gb2312;create database day14;use day14;show tables; create table user( id int primary key, name varchar(20), psw varchar(40), email varchar(40), birthday date ); insert into user(id,name,psw,email,birthday) values(1,'林黛玉','lindaiyu','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(2,'薛宝钗','xuebaochai','[e-mail]','1993-06-07'); insert into user(id,name,psw,email,birthday) values(3,'史湘云','shixiangyun','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(4,'妙玉','miaoyu','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(5,'晴雯','qinwen','[e-mail]','1992-06-07'); insert into user(id,name,psw,email,birthday) values(6,'爱哥哥','igg','[e-mail]','1991-06-07'); select * from user; *//*    public class Driver extends NonRegisteringDriver implements java.sql.Driver {      // Register ourselves with the DriverManager      static {        try {          java.sql.DriverManager.registerDriver(new Driver());        } catch (SQLException E) {          throw new RuntimeException("Can't register driver!");        }      }    }*/public class demo2 {  public static void main(String[] args) throws SQLException, ClassNotFoundException {    /*方法说明：     *jdbc连接标准6步！     */    //1加载驱动    //DriverManager.registerDriver(new com.mysql.jdbc.Driver());    //推荐这种方式:仅依赖一串字符串,并且避免了重复注册    //(Driver的静态代码块已经new过一次,见上面)    Class.forName("com.mysql.jdbc.Driver");    //2获取Connection    String url="jdbc:mysql://localhost:3306/day14?useUnicode=true&characterEncoding=utf-8";    url="jdbc:mysql:///day14?useUnicode=true&characterEncoding=utf-8";    String user="root";    String password="root";//    Connection conn=DriverManager.getConnection(url, user, password);    String url_sql="jdbc:mysql://localhost:3306/day14?user=root&password=root";    String url_oracle="jdbc:oracle:thin:@localhost:1521:day14?user=root&password=root";    String url_sql_server="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=day14?user=root&password=root";    Connection conn=DriverManager.getConnection(url_sql);    //3获取Statement对象,专门用于发送SQL语句    Statement st=conn.createStatement();    //4用Statement向数据库执行查询语句,返回结果集ResultSet    String sql="select * from user";    ResultSet rs=st.executeQuery(sql);    //ResultSet rs = st.executeQuery("select * from user");    //5while循环结果集指针    while (rs.next()) {      //一般是将获取的数据封闭到JavaBean      User user1=new User();      user1.setId(rs.getInt("id"));      user1.setName(rs.getString("name"));      user1.setPsw(rs.getString("psw"));      user1.setEmail(rs.getString("email"));      user1.setBirthday(rs.getDate("birthday"));      /*       * System.out.println("id="+rs.getObject("id"));      System.out.println("name="+rs.getObject("name"));      System.out.println("psw="+rs.getObject("psw"));      System.out.println("email="+rs.getObject("email"));      System.out.println("birthday="+rs.getObject("birthday"));*/    }    //6关闭资源,先ResultSet再Statement最后Connection    rs.close();    st.close();    conn.close();  }}

demo3

package cn.itcast.demo;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import org.junit.Test;import cn.itcast.domain.User;import cn.itcast.utils.JdbcUtils;public class demo3 {  //Junit测试insert插入  @Test  public void insert(){    Connection conn=null;    Statement st=null;    ResultSet rs=null;    try {      conn=JdbcUtils.getConnection();      st=conn.createStatement();      String sql="insert into user(id,name,psw,email,birthday) values(7,'探春','123456','[e-mail]','1992-06-07')";      int num=st.executeUpdate(sql);      if (num>0) {        System.out.println("插入成功!");      }else {        System.out.println("插入失败!");      }    } catch (SQLException e) {      throw new RuntimeException(e);    }finally{      JdbcUtils.release(conn, st, rs);    }  }  //Junit测试update更新  @Test  public void update(){    Connection conn=null;    Statement st=null;    ResultSet rs=null;    try {      conn=JdbcUtils.getConnection();      st=conn.createStatement();      String sql="update user set birthday='1992-06-06' where name='探春'";      int num=st.executeUpdate(sql);      if (num>0) {        System.out.println("更新成功!");      }else {        System.out.println("更新失败!");      }    } catch (SQLException e) {      throw new RuntimeException(e);    }finally{      JdbcUtils.release(conn, st, rs);    }  }  //Junit测试delete删除记录  @Test  public void delete(){    Connection conn=null;    Statement st=null;    ResultSet rs=null;    try {      conn=JdbcUtils.getConnection();      st=conn.createStatement();      String sql="delete from user where name='真事隐'";      int num=st.executeUpdate(sql);      if (num>0) {        System.out.println("删除成功!");      }else {        System.out.println("删除失败!");      }    } catch (SQLException e) {      throw new RuntimeException(e);    }finally{      JdbcUtils.release(conn, st, rs);    }  }  //Junit测试select查询记录  @Test  public void select(){    Connection conn=null;    Statement st=null;    ResultSet rs=null;    try {      conn=JdbcUtils.getConnection();      st=conn.createStatement();      String sql="select id,name,psw,email,birthday from user where name='妙玉'";      rs=st.executeQuery(sql);      User user=null;      if (rs.next()) {        user=new User();        user.setId(rs.getInt("id"));        user.setName(rs.getString("name"));        user.setPsw(rs.getString("psw"));        user.setEmail(rs.getString("email"));        user.setBirthday(rs.getDate("birthday"));      }      System.out.println(user.getName());    } catch (SQLException e) {      throw new RuntimeException(e);    }finally{      JdbcUtils.release(conn, st, rs);    }  }  //Junit测试selectAll查询所有记录  @Test  public void selectAll(){    Connection conn=null;    Statement st=null;    ResultSet rs=null;    try {      conn=JdbcUtils.getConnection();      st=conn.createStatement();      String sql="select id,name,psw,email,birthday from user ";      rs=st.executeQuery(sql);      List list=new ArrayList();      while (rs.next()) {        User user=new User();        user.setId(rs.getInt("id"));        user.setName(rs.getString("name"));        user.setPsw(rs.getString("psw"));        user.setEmail(rs.getString("email"));        user.setBirthday(rs.getDate("birthday"));        list.add(user);      }      System.out.println(list.get(1).getClass());    } catch (SQLException e) {      throw new RuntimeException(e);    }finally{      JdbcUtils.release(conn, st, rs);    }  }}

domain包中的User

package cn.itcast.domain;import java.util.Date;public class User {  private int id;  private String name;  private String psw;  private String email;  private Date birthday;  public int getId() {    return id;  }  public void setId(int id) {    this.id = id;  }  public String getName() {    return name;  }  public void setName(String name) {    this.name = name;  }  public String getPsw() {    return psw;  }  public void setPsw(String psw) {    this.psw = psw;  }  public String getEmail() {    return email;  }  public void setEmail(String email) {    this.email = email;  }  public Date getBirthday() {    return birthday;  }  public void setBirthday(Date birthday) {    this.birthday = birthday;  }}

utils包中的JdbcUtils

package cn.itcast.utils;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JdbcUtils {  private static Properties pro=new Properties();  /*   * 静态成员Properties   * 静态代码块:加载配置文件,注册驱动   * 静态方法1:获取连接   * 静态方法2:释放连接   * 工具类的异常只管抛,也可以转型后抛   * db.properties文件位于类目录下即src   */  static{    String pro_name="db.properties";    InputStream in=JdbcUtils.class.getClassLoader().getResourceAsStream(pro_name);    try {      pro.load(in);      Class.forName(pro.getProperty("driver"));    } catch (Exception e) {      // 静态代码块的异常只能转型后抛出      throw new ExceptionInInitializerError(e);    }  }  //方法1:获取连接  public static Connection getConnection() throws SQLException{    String url=pro.getProperty("url");    String user=pro.getProperty("user");    String password=pro.getProperty("password");    Connection conn=DriverManager.getConnection(url, user, password);    return conn;  }  //方法2:释放连接  public static void release(Connection conn,Statement st,ResultSet rs){    if (conn!=null) {      try {        conn.close();      }catch (Exception e) {        //只能记录!一旦抛出,后面的2条if代码就无法执行了        e.printStackTrace();      }      conn=null;    }    if (st!=null) {      try {        st.close();      }catch (Exception e) {        //只能记录!一旦抛出,后面的1条if代码就无法执行了        e.printStackTrace();      }      st=null;    }    if (rs!=null) {      try {        rs.close();      }catch (Exception e) {        e.printStackTrace();      }      rs=null;    }  }}

src类目录下db.properties

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/day14?useUnicode=true&characterEncoding=utf-8user=rootpassword=root#driver=oracle.jdbc.driver.OracleDriver#url=jdbc:oracle:thin:@localhost:1521:orcl#user=system#password=itcast

用到的MySQL驱动:mysql-connector-java-5.0.8-bin.jar

用到的oracle驱动:ojdbc14.jar