你的位置:首页 > Java教程

[Java教程]JDBC的链接及封装

导入 mysql 的jar包jar包:可以直接拿来用,又不想我们看到源代码 sql语句 一定注意:当update,delete时 一定注意where 条件,一定要写!!!
public static void add() {      // try catch 捕获异常,try有异常会跳到catch      Connection con = null;      try {         // 1.选择要连接哪一种数据库---JDBC加载数据库驱动         Class.forName("com.mysql.jdbc.Driver");         // 2.创建链接(主机名,端口号,用户名,密码)---url包含主机名+端口号+数据库         con = DriverManager.getConnection("jdbc:mysql://localhost:3306/yyy", "root", "123456");         // 3.选择操作的数据库--此动作在上一步的url中集成         // 4.创建命令窗口 写 sql语句         PreparedStatement pstmt = con.prepareStatement("insert into zhuce (name,password,sex,id) value(?,?,?,?)");         pstmt.setString(1, "8888");         pstmt.setString(2, "8888");         pstmt.setString(3, "F");         pstmt.setString(4, "8888");         // 5.运行sql语句 ,查看结果---增删改调用executeUpdate 返回受影响的行数;查询调用 executeQuery         // 返回查询结果集         int result = pstmt.executeUpdate();         System.out.println(result);      } catch (ClassNotFoundException e) {         // TODO Auto-generated catch block         e.printStackTrace();      } catch (SQLException e) {         // TODO Auto-generated catch block         e.printStackTrace();      } finally {         try {            if (con != null) {               con.close();            }         } catch (SQLException e) {            e.printStackTrace();         }      }   }public static void main (String[] args){         add();   }

以上是add方法,update和delete方法只是改变了sql语句

update://根据主键查询PreparedStatement pstmt = con.prepareStatement("update zhuce set sex=?,id=? where name=? and password=?");pstmt.setString(1, "M");pstmt.setString(2, "2222");pstmt.setString(3, "2222");pstmt.setString(4, "1111");delete://删除主键PreparedStatement pstmt = con.prepareStatement("delete from zhuce where name=?and password=?");pstmt.setString(1, "8888");pstmt.setString(2, "8888");

 JDBC的封装

将1,2步  close  sql语句的输出 进行封装

public class JdbcUtil {  public static Connection getConnection(){   Connection con =null;   try {      //1.加载驱动         Class.forName("com.mysql.jdbc.Driver");         //2.创建连接         con=DriverManager.getConnection("jdbc:mysql://localhost:3306/yyy", "root", "123456");      } catch (ClassNotFoundException e) {         // TODO Auto-generated catch block         e.printStackTrace();      } catch (SQLException e) {         // TODO Auto-generated catch block         e.printStackTrace();      }   return con;  }  public static void close(Connection con){   try {      if(con!=null){           con.close();      }      } catch (SQLException e) {         // TODO Auto-generated catch block         e.printStackTrace();      }  }  public static int executeUpdate(String sql,Object[] params){   Connection con = getConnection();   int result =0;   try {         PreparedStatement pstmt = con.prepareStatement(sql);         if(params!=null){           for(int i=0;i<params.length;i++){            pstmt.setObject(i+1, params[i]);           }         }         result = pstmt.executeUpdate();      } catch (SQLException e) {         // TODO Auto-generated catch block         e.printStackTrace();      }finally {         close(con);      }   return result;  }}
JAVABEAN又叫 bean 等跟数据库里面的表发生映射
package com.neuedu.bean;import com.sun.org.apache.简化使用动态数组//一个方法中只能有一个动态参数//动态参数必须位于参数列表的最后一个将之前object[] 改成 动态数组 object...
public static int executeUpdate(String sql,Object... params)

 增删改 方法简化

public static int update(Student student){    return JdbcUtil.executeUpdate("update student set sname=?,age=?,sex=? where sid=?",student.getSname(),student.getAge(),student.getSex(),student.getSid());   }public static int add(Student student) {    return JdbcUtil.executeUpdate("insert into student (sid,sname,age,sex) values(?,?,?,?)", student.getSid(),student.getSname(),student.getAge(),student.getSex());   }public static int delete(int id){    return JdbcUtil.executeUpdate("delete from student where sid=?", id);   }

 

查询不适合用数组,因为不知道有多少数据;用集合,集合有两种  ArrayList,LinkedList基于ArrayList(Vector,ArrayList)适合查询,而LinkedList(链表)适合添加,删除操作。用到 游标 的操作:rs.next() ,判断有没有下一行数据,有的话 游标推到下一行,并返回true ;反之 返回false 。 首先从最上面开始第一次调用rs.next() 就会调用首行的下一行,也就是第一行数据
public static List<Student> getStudents(){    Connection con = null;    List<Student> list = new ArrayList<>();    try {       Class.forName("com.mysql.jdbc.Driver");       con=DriverManager.getConnection("jdbc:mysql://localhost:3306/yyy", "root", "123456");       PreparedStatement pstmt = con.prepareStatement("select * from student");       ResultSet rs = pstmt.executeQuery();       while(rs.next()){          Student student=new Student();          student.setSid(rs.getInt("sid"));          student.setSname(rs.getString("sname"));          student.setSname(rs.getString("age"));          student.setSname(rs.getString("sex"));          list.add(student);       }    } catch (ClassNotFoundException e) {       // TODO Auto-generated catch block       e.printStackTrace();    } catch (SQLException e) {      // TODO Auto-generated catch block        e.printStackTrace();    }finally{       try {          if(con!=null){             con.close();          }       } catch (Exception e2) {          // TODO: handle exception        }    }    return list;  }}public static void main (String[] args){  List<Student> students=getStudents();  System.out.println(students);}