首页 > 代码库 > 第十八章 JDBC

第十八章 JDBC

1.使用jdbc连接数据库:

  a.导入驱动jar包
  b.Class.forName(DRIVER);
  // 2.建立连接
  con = DriverManager.getConnection(URL, USERNAME, PWD);

2.增、删、改:(只要修改sql就可实现)
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.SQLException;
  import java.sql.Statement;

  public class UpadateTest {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/petstore";
    public static final String USERNAME = "root";
    public static final String PWD = "root";

    public static void main(String[] args) {
      Connection con = null;
      Statement statement = null;
      try {
        Class.forName(DRIVER);
        con = DriverManager.getConnection(URL, USERNAME, PWD);
        statement = con.createStatement();
        String sql = "update pet set name=‘毛毛‘ where id=2";
        int result = statement.executeUpdate(sql);//增删改
        if (result > 0) {
          System.out.println("修改成功");
        } else {
          System.out.println("修改失败");
        }
      } catch (ClassNotFoundException e) {
        e.printStackTrace();
      } catch (SQLException e) {
        e.printStackTrace();
      }finally{
        try {
          statement.close();
          con.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }

3.查询:
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;

  public class QueryTest {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/petstore";
    public static final String USERNAME = "root";
    public static final String PWD = "root";

    public static void main(String[] args) {
      Connection con = null;
      Statement statement = null;
      ResultSet rs=null;
      try {
        Class.forName(DRIVER);
        con = DriverManager.getConnection(URL, USERNAME, PWD);
        statement = con.createStatement();
        String sql = "select * from pet";
        rs = statement.executeQuery(sql);
        while (rs.next()) {
          System.out.print(rs.getInt(1)+"\t");
          System.out.print(rs.getString(2)+"\t");
          System.out.print(rs.getInt(3)+"\t");
          System.out.print(rs.getInt(4)+"\t");
          System.out.print(rs.getString(5)+"\n");
        }
      } catch (ClassNotFoundException e) {
        e.printStackTrace();
      } catch (SQLException e) {
        e.printStackTrace();
      }finally{
        try {
          rs.close();
          statement.close();
          con.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }

    }
   }

4.statement的方法的区别:
  execute     不常用    返回值是boolean类型,true指返回结果集
  executeUpdate   增、删、改  返回int类型,指受影响的行数
  executeQuery    查       返回ResultSet类型,指结果集

5.prepareStatement避免sql注入异常
  使用方式:
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.List;

  public class PrepTest {
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/petstore";
    public static final String USERNAME = "root";
    public static final String PWD = "root";

    public static void main(String[] args) {
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      List<Master> masterlst = new ArrayList<Master>();
      try {
        // 1加载驱动
        Class.forName(DRIVER);
        // 2创建连接
        con = DriverManager.getConnection(URL, USERNAME, PWD);
        // 3声明Sql
        //  StringBuffer sql = new StringBuffer("select * from master where 1=1 ");
             // sql.append("and name=? ");
        // sql.append("and money=?");
        String sql="select * from master ";
        // 4.创建PreparedStatement对象
        ps = con.prepareStatement(sql);
             // 5.传参数
          // ps.setString(1, "李");
          // ps.setInt(2, 100);
            // 6.执行sql
          rs = ps.executeQuery();
            // 7遍历显示
        while (rs.next()) {
          Master m = new Master();
          m.setId(rs.getInt(1));
          m.setName(rs.getString(2));
          m.setPassword(rs.getString(3));
          m.setMoney(rs.getInt(4));
          masterlst.add(m);
        }
      } catch (ClassNotFoundException e) {
        e.printStackTrace();
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        // 8.关闭对象//由内到外关闭
        try {
          rs.close();
          ps.close();
          con.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }

      System.out.println("查询结果:");
      for (Master m:masterlst) {
          System.out.println(m.getId()+"\t"+m.getName()+"\t"+m.getPassword()+"\t"+m.getMoney());
      }
    }

  }

 

第十八章 JDBC