[关闭]
@MRsunhuimin 2019-08-12T14:16:55.000000Z 字数 9130 阅读 224

JDBC连接(08.12)

web

作者：孙慧敏

1. jdbc连接数据库步骤

准备jar
mysql-connector-java-5.1.0-bin.jar

  • 加载驱动Class.forName("com.mysql.jdbc.Driver");
  • 使用DriverManager获取数据库connection连接
  • 创建Statement对象 用于执行SQL语句
  • 执行SQL语句
  • 释放资源
  1. //jdbc连接数据库，使用Statement接口 
  2. package com.hz.test;
  4. import java.sql.*;
  6. public class Test1 {
  7.     public static void main(String[] args) {
  8.         Connection conn = null;
  9.         Statement stmt = null;
  10.         ResultSet rs = null;
  12.         try {
  13.             //加载驱动  方言
  14.             Class.forName("com.mysql.jdbc.Driver");
  15.             //准备数据库连接路径
  16.             String url = "jdbc:mysql://192.168.0.157:3306/xxshop";
  17.             //用户名与密码
  18.             String username = "root";
  19.             String userpwd = "root";
  20.             //根据路径，用户名，密码 使用DriverManager获取数据库connection连接
  21.             conn =DriverManager.getConnection(url,username,userpwd);
  22.             //准备要执行的SQL语句
  23.             String sql = "SELECT user_id,user_name FROM sf_user";
  24.             //创建Statement对象  用于执行SQL语句
  25.             stmt = conn.createStatement();
  26.             //执行SQL语句
  27.             rs =stmt.executeQuery(sql);
  28.             //处理ResultSet结果集
  29.             //rs.next()  返回boolean 值
  30.             while (rs.next()){
  31.                 //long id = rs.getLong(1);
  32.                 //String name = rs.getString(2);
  33.                 long id = rs.getLong("user_id");
  34.                 String name = rs.getString("user_name");
  35.                 System.out.println("用户id:"+id+"用户名:"+name);
  36.             }
  37.         } catch (Exception e) {
  38.             e.printStackTrace();
  39.         }finally {
  40.             try {
  41.                 //释放资源
  42.                 rs.close();
  43.                 stmt.close();
  44.                 conn.close();
  45.             } catch (SQLException e) {
  46.                 e.printStackTrace();
  47.             }
  48.         }
  49.     }
  50. }

2. 三种数据库驱动

com.microsoft.jdbc.sqlserver.SQLServerDriver(SQL Server) 

com.mysql.jdbc.Driver(MySql) 

oracle.jdbc.driver.OracleDriver(Oracle)

3. Statement常用对象

    ResultSet executeQuery（sql）
    执行sql查询语句，并返回ResultSet对象

    int executeUpdate(sql)
    执行insert,update，delete语句，返回受影响行数

    boolean execute(sql)
    执行insert,update，delete语句，返回true或false false成功

4. 防止数据库乱码及日期出错

    放入url连接路径之后

    ?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull

5. PreparedStatement对象使用

    防止SQL注入，使用占位符“?”方式进行SQL拼接

6. 练习，使用PreparedStatement进行增删改查等

  1. package com.hz.test;
  3. import com.hz.entity.Goods;
  4. import com.hz.entity.User;
  6. import java.sql.*;
  7. import java.util.ArrayList;
  8. import java.util.List;
  10. public class Tset2 {
  11.     //验证
  12.     public User loginUser(String user_name, String user_password){
  13.         Connection conn = null;
  14.         PreparedStatement ps = null;
  15.         ResultSet rs = null;
  16.         try {
  17.             Class.forName("com.mysql.jdbc.Driver");//加载驱动  方言
  18.             String url = "jdbc:mysql://192.168.0.157:3306" +
  19.                     "/xxshop?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
  20.             String username = "root";
  21.             String userpwd = "root";
  22.             conn = DriverManager.getConnection(url,username,userpwd);//获取Connection对象
  23.             String sql = "select user_id,user_name,user_password from sf_user where user_name=? and user_password=?";
  24.             ps = conn.prepareStatement(sql);
  25.             ps.setString(1,user_name);//第一个占位符处
  26.             ps.setString(2,user_password);//ps.setXXXX(位置<从1开始>,值<参数>)
  27.             rs = ps.executeQuery();//执行查询
  28.             User user = null;//用于判断 等于null登录失败，否则成功
  29.             while (rs.next()){
  30.                 user = new User();//进入循环，登录成功，创建user对象
  31.                 user.setUser_id(rs.getLong("user_id"));//使用rs.getXXX("返回的列名") 放入user对象
  32.                 user.setUser_name(rs.getString("user_name"));
  33.                 user.setUser_password(rs.getString("user_password"));
  34.             }
  35.             return user;
  37.         } catch (Exception e) {
  38.             e.printStackTrace();
  39.         } finally {
  40.             try {
  41.                 ps.close();
  42.                 conn.close();
  43.             } catch (SQLException e) {
  44.                 e.printStackTrace();
  45.             }
  47.         }
  48.         return null;
  49.     }
  52.     //查询
  53.     public void selectUser(){
  54.         Connection conn = null;
  55.         PreparedStatement ps = null;
  56.         ResultSet rs = null;
  57.         try {
  58.             Class.forName("com.mysql.jdbc.Driver");
  59.             String url = "jdbc:mysql://192.168.0.157:3306" +
  60.                     "/xxshop?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
  61.             String username = "root";
  62.             String userpwd = "root";
  63.             conn = DriverManager.getConnection(url,username,userpwd);
  64.             String sql = "select user_id,user_name from sf_user";
  65.             ps = conn.prepareStatement(sql);
  66.             rs = ps.executeQuery();
  67.             while (rs.next()){
  68.                 long id = rs.getLong("user_id");
  69.                 String name = rs.getString("user_name");
  70.                 System.out.println("用户ID："+id+"\t"+"用户名："+name);
  71.             }
  73.         } catch (Exception e) {
  74.             e.printStackTrace();
  75.         } finally {
  76.             try {
  77.                 ps.close();
  78.                 conn.close();
  79.             } catch (SQLException e) {
  80.                 e.printStackTrace();
  81.             }
  83.         }
  84.     }
  86.     //增加
  87.     public boolean addUser(){
  88.         Connection conn = null;
  89.         PreparedStatement ps = null;
  90.         boolean i = false;
  91.         try {
  92.             Class.forName("com.mysql.jdbc.Driver");
  93.             String url = "jdbc:mysql://192.168.0.157:3306" +
  94.                     "/xxshop?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
  95.             String username = "root";
  96.             String userpwd = "root";
  97.             conn = DriverManager.getConnection(url,username,userpwd);
  98.             String sql = "INSERT INTO sf_user(user_name,user_password) VALUES(?,?)";
  99.             ps = conn.prepareStatement(sql);
  100.             ps.setString(1,"李四");
  101.             ps.setString(2,"qwert");
  103.             if (ps.executeUpdate()>0){
  104.                 i=true;
  105.                 System.out.println("添加成功");
  106.             }else{
  107.                 System.out.println("添加失败!");
  108.             }
  110.         } catch (Exception e) {
  111.             e.printStackTrace();
  112.         } finally {
  113.             try {
  114.                 ps.close();
  115.                 conn.close();
  117.             } catch (SQLException e) {
  118.                 e.printStackTrace();
  119.             }
  121.         }
  122.         return i;
  123.     }
  125.     //删除
  126.     public boolean deleteUser(){
  127.         Connection conn = null;
  128.         PreparedStatement ps = null;
  129.         boolean i = false;
  130.         try {
  131.             Class.forName("com.mysql.jdbc.Driver");
  132.             String url = "jdbc:mysql://192.168.0.157:3306" +
  133.                     "/xxshop?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
  134.             String username = "root";
  135.             String userpwd = "root";
  136.             conn = DriverManager.getConnection(url,username,userpwd);
  137.             String sql = "delete from sf_user where user_id>10";
  138.             ps = conn.prepareStatement(sql);
  139.             if (ps.executeUpdate()>0){
  140.                 i=true;
  141.                 System.out.println("删除成功");
  142.             }else{
  143.                 System.out.println("删除失败!");
  144.             }
  146.         } catch (Exception e) {
  147.             e.printStackTrace();
  148.         } finally {
  149.             try {
  150.                 ps.close();
  151.                 conn.close();
  153.             } catch (SQLException e) {
  154.                 e.printStackTrace();
  155.             }
  157.         }
  158.         return i;
  159.     }
  161.     //修改
  162.     public boolean updateUser(){
  163.         Connection conn = null;
  164.         PreparedStatement ps = null;
  165.         boolean i = false;
  166.         try {
  167.             Class.forName("com.mysql.jdbc.Driver");
  168.             String url = "jdbc:mysql://192.168.0.157:3306" +
  169.                     "/xxshop?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
  170.             String username = "root";
  171.             String userpwd = "root";
  172.             conn = DriverManager.getConnection(url,username,userpwd);
  173.             String sql = "update sf_user set user_name='赵六',user_password='123456' where user_id=3";
  174.             ps = conn.prepareStatement(sql);
  175.             if (ps.executeUpdate()>0){
  176.                 i=true;
  177.                 System.out.println("修改成功");
  178.             }else{
  179.                 System.out.println("修改失败!");
  180.             }
  182.         } catch (Exception e) {
  183.             e.printStackTrace();
  184.         } finally {
  185.             try {
  186.                 ps.close();
  187.                 conn.close();
  189.             } catch (SQLException e) {
  190.                 e.printStackTrace();
  191.             }
  193.         }
  194.         return i;
  195.     }
  197.     //获取所有商品(goods)信息
  198.     public List<Goods> getGoodsList(){
  199.         Connection conn = null;
  200.         PreparedStatement ps = null;
  201.         ResultSet rs = null;
  202.         try {
  203.             Class.forName("com.mysql.jdbc.Driver");
  204.             String url = "jdbc:mysql://127.0.0.1:3306" +
  205.                     "/xxshop?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
  206.             String username = "root";
  207.             String userpwd = "root";
  208.             conn = DriverManager.getConnection(url,username,userpwd);
  209.             String sql = "SELECT goods_id,goods_title,goods_browse,goods_state FROM sf_goods ";
  210.             ps = conn.prepareStatement(sql);
  211.             rs = ps.executeQuery();
  212.             List<Goods> goodsList = new  ArrayList<Goods>();
  213.             while (rs.next()){
  214.                 Goods goods = new Goods();
  215.                 goods.setGoods_id(rs.getLong("goods_id"));
  216.                 goods.setGoods_title(rs.getString("goods_title"));
  217.                 goods.setGoods_browse(rs.getInt("goods_browse"));
  218.                 goods.setGoods_state(rs.getInt("goods_state"));
  219.                 goodsList.add(goods);
  220.             }
  221.             return goodsList;
  222.         } catch (Exception e) {
  223.             e.printStackTrace();
  224.         } finally {
  225.             try {
  226.                 ps.close();
  227.                 conn.close();
  229.             } catch (SQLException e) {
  230.                 e.printStackTrace();
  231.             }
  233.         }
  234.         return null;
  235.     }
  237.     //修改商品信息
  238.     public int updateGoods(Goods goods){
  239.         Connection conn = null;
  240.         PreparedStatement ps = null;
  241.         try {
  242.             Class.forName("com.mysql.jdbc.Driver");
  243.             String url = "jdbc:mysql://127.0.0.1:3306" +
  244.                     "/xxshop?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull";
  245.             String username = "root";
  246.             String userpwd = "root";
  247.             conn = DriverManager.getConnection(url,username,userpwd);
  248.             String sql = "UPDATE sf_goods SET goods_title = ?,goods_browse = ?," +
  249.                     "goods_state = ? WHERE goods_id = ?";
  250.             ps = conn.prepareStatement(sql);
  251.             ps.setString(1,goods.getGoods_title());
  252.             ps.setInt(2,goods.getGoods_browse());
  253.             ps.setInt(3,goods.getGoods_state());
  254.             ps.setLong(4,goods.getGoods_id());
  255.             int i = ps.executeUpdate();
  256.             return i;
  257.         } catch (Exception e) {
  258.             e.printStackTrace();
  259.         } finally {
  260.         }
  261.         return 0;
  262.     }
  264.     public static void main(String[] args) {
  265.         Tset2 t = new Tset2();
  267. //        Goods goods = new Goods();
  268. //        goods.setGoods_id(2);
  269. //        goods.setGoods_title("lalala");
  270. //        goods.setGoods_browse(99);
  271. //        goods.setGoods_state(100);
  272. //        int i = t.updateGoods(goods);
  273. //        if (i>0){
  274. //            System.out.println("修改成功");
  275. //        }else {
  276. //            System.out.println("修改失败");
  277. //        }
  279. //        List<Goods> goodsList = t.getGoodsList();
  280. //        for (Goods gd:goodsList){
  281. //            System.out.println("商品ID:"+gd.getGoods_id());
  282. //            System.out.println("商品标题:"+gd.getGoods_title());
  283. //            System.out.println("浏览次数:"+gd.getGoods_browse());
  284. //            System.out.println("商品状态(0未上架1在售)；"+gd.getGoods_state());
  285. //        }
  287. //        User user = t.loginUser("张三","111");
  288. //        if (user==null) {
  289. //            System.out.println("登录失败！");
  290. //        } else {
  291. //            System.out.println("欢迎"+user.getUser_name()+"登陆成功！");
  292. //        }
  294.         //t.selectUser();
  295.         //t.addUser();
  296.         //t.deleteUser();
  297.         //t.updateUser();
  298.     }
  299. }
  1. //实体类User
  2. package com.hz.entity;
  4. public class User {
  5.     private long user_id;
  6.     private String user_name;
  7.     private String user_password;
  9.     public long getUser_id() {
  10.         return user_id;
  11.     }
  13.     public void setUser_id(long user_id) {
  14.         this.user_id = user_id;
  15.     }
  17.     public String getUser_name() {
  18.         return user_name;
  19.     }
  21.     public void setUser_name(String user_name) {
  22.         this.user_name = user_name;
  23.     }
  25.     public String getUser_password() {
  26.         return user_password;
  27.     }
  29.     public void setUser_password(String user_password) {
  30.         this.user_password = user_password;
  31.     }
  32. }
  1. //实体类Goods
  2. package com.hz.entity;
  4. public class Goods {
  5.     private long goods_id;//商品id
  6.     private String goods_title;//商品标题
  7.     private int goods_browse;//商品浏览次数
  8.     private int goods_state;//商品状态
  11.     public long getGoods_id() {
  12.         return goods_id;
  13.     }
  15.     public void setGoods_id(long goods_id) {
  16.         this.goods_id = goods_id;
  17.     }
  19.     public String getGoods_title() {
  20.         return goods_title;
  21.     }
  23.     public void setGoods_title(String goods_title) {
  24.         this.goods_title = goods_title;
  25.     }
  27.     public int getGoods_browse() {
  28.         return this.goods_browse;
  29.     }
  31.     public void setGoods_browse(int goods_browse) {
  32.         this.goods_browse = goods_browse;
  33.     }
  35.     public int getGoods_state() {
  36.         return this.goods_state;
  37.     }
  39.     public void setGoods_state(int goods_state) {
  40.         this.goods_state = goods_state;
  41.     }
  43. }
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注