JDBC

来源：互联网发布：mui vue.js 集成编辑：程序博客网时间：2024/06/10 15:20

JDBC

1) JDBC概述：

JDBC(JavaDatabase Connectify)是Java语言访问数据库的一种规范和API。它提供了一组与平台无关的、可用于连接数据库及执行SQL语句的API。

JDBC API中主要的接口和类：

java.sql.DriverManager //管理JDBC 驱动程序获取Connection对象

java.sql.Connection //建立与数据库特定的连接用于执行sql语句

java.sql.Statement //用于执行sql语句

java.sql.PreparedStatement //预编译的Statement 它是Statement子接口

java.sql.CallableStatement //用于执行存储过程的Statement

java.saql.ResultSet //结果集对象

java.sql.SQLException //提供关于数据库访问错误或其他错误信息的异常

使用JDBC操作SQLServer数据库的步骤是：

a) 下载SQL Server JDBC驱动包（下载解压后会产生sqljdbc.jar和 sqljdbc4.jar供连接数据库）

b) 配置数据库驱动（在项目中导入sqljdbc4.jar文件《右键项目点击propertieslibraries Add External JARs 选择 sqljdbc4.jar文件》）

c) 加载数据库驱动Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver”)

d) 通过DriverManager获取数据库连接。Driver.Manager.getConnection(“url”,”user”,”password”)

e) Connection对象的createStatement()用于创建Statement对象；Connection对象的preparedStatement(sql)用于创建PreparedStatement对象。

d) Statement对象的executeUpdate(),用于执行DML语句；PreparedStatementde setXxx(int index,Xxx value)用于给SQL占位赋值。

//封装实体Wujiang类public class Wujiang {  private int id;  private String name;   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  Wujiang(){}public  Wujiang(int id,String name){this.id=id;this.name=name; }}//封装DBManagerpackage com.DBManager;import java.sql.*;public class DBManager {    //SQL Server JDBC 连接驱动名称private static final String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";//数据库连接地址 DataBaseName数据库名private static final String url="jdbc:sqlserver://127.0.0.1:1433;DataBaseName=SanGuoSha";static Connection conn=null;    static PreparedStatement pst=null;    static ResultSet rst=null;        private static final String user="sa";    private static final String password="123456";    /**     * 数据库连接     */public static Connection  getconnection( ){try {Class.forName(driver);//加载驱动//获得数据库连接conn = DriverManager.getConnection(url, user, password);} catch (Exception e) {}return conn;}/** * 一般关闭连接 以节约数据库资源 */public static void  getclose(PreparedStatement pst,ResultSet rst,Connection conn){try {if (pst!=null) {pst.close();pst=null;}if (rst!=null) {rst.close();rst=null;}if (conn!=null) {conn.close();conn=null;}} catch (Exception e) {}}}//封装增删改查方法在主函数中调用package com.JDBCPackage;import java.sql.*;import java.util.*;import com.DBManager.DBManager;import com.Models.Wujiang;public class TestFengzhuan {static Connection conn=null;    static PreparedStatement pst=null;    static ResultSet rst=null;        /**     * 查詢     * @return     */    public  List<Wujiang> GetallSelectWujang(){      List<Wujiang> listwujiang = new ArrayList<Wujiang>();                try {          //调用JDBC的公共类DBmanager的getconnection()获取COnnection对象      conn = DBManager.getconnection();            String sql="select * from Wujiang";      pst=conn.prepareStatement(sql);      System.out.println("编号\t姓名);      rst = pst.executeQuery();      while (rst.next()) {  int id=rst.getInt(1);  String name=rst.getString(2);      Wujiang wujiang=new Wujiang(id, name);  listwujiang.add(wujiang);     System.out.print(id+"\t");   System.out.print(name+"\t");    }  } catch (Exception e) {    }          finally{          DBManager.getclose(pst, rst, conn);          }          return listwujiang;    }        /**     * 增加武将类     * @return     */    public int Insertwujang(){    int row=0;    try {conn = DBManager.getconnection();String sql="insert into Wujiang(id,name) values(?,?)";pst = conn.prepareStatement(sql);pst.setInt(1, 10);pst.setString(2, "aa");row = pst.executeUpdate();if(row>0){System.out.println("添加成功");}} catch (Exception e) {// TODO: handle exception}    finally{    DBManager.getclose(pst, null, conn);    }    return row;    }    /**     * 删除武将记录     * @return     */    public int Deletewujang(){    int row=0;    try {conn = DBManager.getconnection();String sql="delete from Wujiang where id=?";pst=conn.prepareStatement(sql);pst.setInt(1, 10);//传参row = pst.executeUpdate();if (row!=0) {System.out.println("删除武将记录成功！");}else{System.out.println("删除武将记录失败！");}} catch (Exception e) {// TODO: handle exception}    finally{    DBManager.getclose(pst, null, conn);    }    return row;    }     /**   * 修改武将记录   * @param sql   * @param params   */    public void Updatewujiang(String sql,Object[] params){try {conn=DBManager.getconnection();pst=conn.prepareStatement(sql);//给占位符参数值数组的每个元素赋值for (int i = 0; i < params.length; i++) {pst.setObject(i+1, params[i]);}int num = pst.executeUpdate();if (num>0) {System.out.println("数据更改或删除成功！");}} catch (Exception e) {e.printStackTrace();}finally{DBManager.getclose(pst, null, conn);}}}package com.JDBCPackage;public class testmainDiaoYong {/** * @param args */public static void main(String[] args) {TestFengzhuan t=new TestFengzhuan();//查询所有t.GetallSelectWujang();System.out.println("==========================================");//增加武将记录t.Insertwujang();//修改武将记录String  sql="Update Wujiang set skill=? where name=?";Object[] params={"奸雄12","曹操"};t.Updatewujiang(sql, params);//删除武将记录t.Deletewujang();}}

0 0