数据库_jdbc_入门

于 2013-12-18 19:08:45 发布
阅读量851 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 数据库 文章标签： jdbc 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/pre_eminent/article/details/17399863
本文介绍了一个使用Java JDBC进行数据库操作的示例项目，包括插入、更新、删除和查询记录等功能，并通过JUnit进行了测试。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

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 -proot
set 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','lindaiyu@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(2,'薛宝钗','xuebaochai','xuebaochai@163.com','1993-06-07');
 insert into user(id,name,psw,email,birthday) values(3,'史湘云','shixiangyun','shixiangyun@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(4,'妙玉','miaoyu','miaoyu@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(5,'晴雯','qinwen','qinwen@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(6,'爱哥哥','igg','igg@163.com','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 -proot
set 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','lindaiyu@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(2,'薛宝钗','xuebaochai','xuebaochai@163.com','1993-06-07');
 insert into user(id,name,psw,email,birthday) values(3,'史湘云','shixiangyun','shixiangyun@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(4,'妙玉','miaoyu','miaoyu@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(5,'晴雯','qinwen','qinwen@163.com','1992-06-07');
 insert into user(id,name,psw,email,birthday) values(6,'爱哥哥','igg','igg@163.com','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','tanchun@163.com','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.Driver
url=jdbc:mysql://localhost:3306/day14?useUnicode=true&characterEncoding=utf-8
user=root
password=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

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值