JDBC
1) JDBC概述:
JDBC(JavaDatabase Connectify)是Java语言访问数据库的一种规范和API。它提供了一组与平台无关的、可用于连接数据库及执行SQL语句的API。
2)
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 //提供关于数据库访问错误或其他错误信息 的异常
3)
使用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对象。
//封装实体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;
}
}
//封装DBManager
package 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();
}
}