Java操作数据库--以SQL Server为例
crud介绍(增、删、改、查操作)
CRUD是指在做计算处理时的增加(Create)、查询(Retrieve)(重新得到数据)、更新(Update)和删除(Delete)几个单记事的首字母简写。主要被用在描述软件系统中数据库或者持久层的基本操作功能。
crud介绍
要对数据表进行增、删、改、查，首先要清楚jdbc基本的概念：
JDBC有两种，一种原sun公司提供的数据库连接api但不是直接连接sql server而是先连接ODBC再通过ODBC对sql server进行操作；一种是由微软提供的JDBC数据库连接api可直接对sql server数据库进行操作。
JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，同时，JDBC也是个商标名。
有了JDBC，向各种关系数据发送SQL语句就是一件很容易的事。换言之，有了JDBC API，就不必为访问Sybase数据库专门写一个程序，为访问Oracle数据库又专门写一个程序，或为访问Informix数据库又编写另一个程序等等，程序员只需用JDBC API写一个程序就够了，它可向相应数据库发送SQL调用。同时，将Java语言和JDBC结合起来使程序员不必为不同的平台编写不同的应用程序，只须写一遍程序就可以让它在任何平台上运行，这也是Java语言“编写一次，处处运行”的优势。
注：JDBC访问不同的数据库使用的JDBC API都有所不同，虽然统称为JDBC。如：访问sql server数据库的JDBC，是不能访问oracle数据库的，同理也是也不访问mysql、db2、Informix数据库等。只是访问方式不同，对数据库的操作依然是使用sql语句操作。
jdbc的驱动的分类
目前比较常见的JDBC驱动程序可分为以下四个种类
1、jdbc-odbc桥连接
2、本地协议纯java驱动程序
3、网络协议纯java驱动程序
4、本地api
jdbc不足
尽管JDBC在JAVA语言层面实现了统一，但不同数据库仍旧有许多差异。为了更好地实现跨数据库操作，于是诞生了Hibernate项目，Hibernate是对JDBC的再封装，实现了对数据库操作更宽泛的统一和更好的可移植性。
jdbc-odbc桥连的方式来操作sql server数据库
PS:SQL文件可以到我写的上一篇博客“Java数据库基础”中找到。
/**
* 使用JDBC-ODBC桥连方式操作数据库 db中的emp,dept表
* 1.配置数据源
* 2.在程序中连接数据源
*/
package com.db;
import java.sql.*;
public class db1 {
public static void main(String[] args) {
Connection ct = null;
Statement sm = null;
ResultSet rs = null;
try {
//1.加载驱动(把需要的驱动程序加入内存)
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//2.得到连接(指定连接哪个数据源，用户和密码)
//配置数据源时，选择Windows NT验证，则不需要账号密码
ct = DriverManager.getConnection("jdbc:odbc:mytest");
//3.创建Statement或者PreparedStatement
//Statement用处是：发送sql语句到数据库
sm = ct.createStatement();
//4.执行(crud,创建数据库，备份数据库，删除数据库。。。)
//添加一条数据到dept表
//executeUpdate可以执行 cud操作(添加，删除，修改)
//String sql = "insert into dept values(50, '保安部', '北京')";
//int res = sm.executeUpdate(sql);
//if(res == 1){
//System.out.println("加入一条语句");
//} else {
//System.out.println("添加失败");
//}
//从dept删除一条记录
//String sql = "delete from dept where deptno='50'";
//int res = sm.executeUpdate(sql);
//
//if(res == 1){
//System.out.println("delete.");
//} else {
//System.out.println("failure");
//}
//修改deptno=40的loc改为beijing
//String sql = "update dept set loc = 'beijing' where deptno='40'";
//int res = sm.executeUpdate(sql);
//if(res == 1){
//System.out.println("update.");
//} else {
//System.out.println("failure");
//}
//显示所有的部门
//ResultSet结果集
String sql = "select * from dept";
rs = sm.executeQuery(sql);
//rs指向结果集的第一行的前一行
while(rs.next()){
//取出第一行
int deptno = rs.getInt(1);
String dname = rs.getString(2);
String loc = rs.getString(3);
System.out.println(deptno + " " + dname + " " + loc);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
//谁后创建，谁先关闭
try {
if(rs != null){
rs.close();
}
if(sm != null){
sm.close();
}
if(ct != null){
ct.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
Statement和PreparedStatement的区别(1)
Statemen和PreparedStatement都可以用于把sql语句从java程序中发送到指定数据库，并执行sql语句，但是他们也存在区别：
1、直接使用Statement，驱动程序一般不会对sql语句作处理而直接交给数据库；使用PreparedStatement，形成预编译的过程，并且会对语句作字符集的转换(至少在sql server)中如此。
如此，有两个好处：对于多次重复执行的语句，使用PreparedStament效率会更高一点，并且在这种情况下也比较适合使用batch；另外，可以比较好地解决系统的本地化问题。
2、PreparedStatement还能有效的防止危险字符的注入，也就是sql注入的问题。
Statement和PreparedStatement的区别(2)
看下面两段程序片断：
Code Fragment 1:
String updateString="UPDATE COFFEES SET SALES=75"+"WHERE COF_NAME LIKE 'Colombian'";
stmt.executeUpdate(updateString);
Code Fragment 2:
PreparedStatement updateSales=con.prepareStatement("UPDATE COFFEES SET SALES=? WHERE COF_NAME LIKE ?");
updateSales.setInt(1,75);
updateSales.setString(2,"Colombian");
updateSales.executeUpdate();
后者使用了PreparedStatement，而前者是Statement，PreparedStatement不仅包含了SQL语句，而且大多数情况下这个语句已被预编译过，当其执行时，只需DBMS运行SQL语句，而不必先编译。当你需要执行Statement对象多次的时候，PreparedStatement对象将会降低运行时间，加快了访问数据库的速度。
好处是，不必重复SQL语句的句法，而只需要改其中变量的值，便可重新执行SQL语句。选择PreparedStatement对象与否，在于相同句法的SQL语句是否执行了多次，而且两次之间的差别仅是变量的不同。如仅执行一次的话，它和普通的对象无差异，体现不出预编译的优越性。
/**
* PreparedStatement使用CRUD
* 1.PreparedStatement 可以提高执行效率(因为有预编译功能)
* 2.PreparedStatement 可以防止sql注入，但是要用?赋值的方式
*/
package com.db;
import java.sql.*;
public class db2 {
public static void main(String[] args) {
//定义对象
PreparedStatement ps = null;
Connection ct = null;
ResultSet rs = null;
try {
//加载驱动
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//得到连接
ct = DriverManager.getConnection("jdbc:odbc:mytest");
////查询
////创建PreparedStatement
//ps = ct.prepareStatement("select * from dept where deptno=? and loc=?");
////设置参数
//ps.setInt(1, 20);
//ps.setString(2, "dallas");
////执行查询
//rs = ps.executeQuery();
//
//while(rs.next()){
//System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3));
//}
//插入
//创建PreparedStatement
ps = ct.prepareStatement("insert into dept values(?,?,?)");
//设置参数
ps.setInt(1, 50);
ps.setString(2, "deploy");
ps.setString(3, "beijing");
//执行插入
int res = ps.executeUpdate();
if(res == 1){
System.out.println("insert.");
} else {
System.out.println("failure.");
}
} catch (Exception e) {
e.printStackTrace();
} finally{
//关闭资源
try{
if(rs != null){
rs.close();
}
if(ct != null){
ct.close();
}
if(ps != null){
ps.close();
}
}catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
JDBC-ODBC桥连操作sql server与JDBC驱动直连操作sql server的区别：
1、JDBC-ODBC桥连sql server无需引入外部驱动
2、JDBC直连需要引入微软提供的JDBC驱动
/**
* jdbc方式操纵数据库
* 1.引入java.sql.*;
* 2.引入jar包
* 3.sqlserver2000需要引入三个jar包,分别是msbase.jar和mssqlserver.jar和msutil.jar
* 4.sqlserver2005/2008/2012版本中可以引入sqljdbc.jar或sqljdbc4.jar两个微软提供的JDBC包，官方目前推出2.0/3.0/4.0版本
* 5.使用sqljdbc4.jar后可以不使用加载驱动Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");而直接连接数据库
* 6.使用sqljdbc.jar则需要加载驱动
* 7.特别说明，如果取值是按编号取，则需一一对应；如果按字段列名取值，则可以灵活取值
*/
package com.db;
import java.sql.*;
public class db3 {
public static void main(String[] args) {
// 定义
Connection ct = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 初始化对象
// 1.加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// 2.得到连接
ct = DriverManager.getConnection(
"jdbc:sqlserver://127.0.0.1:1433; DatabaseName=db", "sa",
"123456");
// //3.创建PraparedStatement
// ps = ct.prepareStatement("select * from emp");
// //4.执行(如果是增删改使用executeUpdate(),如果是查询则使用executeQuery())
// rs = ps.executeQuery();
//
// //循环取出员工的名字，薪水，部门编号
// while(rs.next()){
// String ename = rs.getString("ename");
// float sal = rs.getFloat(6);
// int deptno = rs.getInt(8);
// System.out.println(ename + " " + sal + " " + deptno);
// }
// //3.创建PraparedStatement
// ps =
// ct.prepareStatement("select ename,sal,dname from emp,dept where emp.deptno = dept.deptno ");
// //4.执行(如果是增删改使用executeUpdate(),如果是查询则使用executeQuery())
// rs = ps.executeQuery();
//
// //循环取出员工的名字，薪水，部门名称
// while(rs.next()){
// String ename = rs.getString("ename");
// float sal = rs.getFloat("sal");
// String deptName = rs.getString("dname");
// System.out.println(ename + " " + sal + " " + deptName);
// }
// 3.创建PraparedStatement
ps = ct.prepareStatement("insert into dept values(?,?,?)");
// 4.执行(如果是增删改使用executeUpdate(),如果是查询则使用executeQuery())
// 赋值
ps.setInt(1, 60);
ps.setString(2, "software");
ps.setString(3, "beijing");
int res = ps.executeUpdate();
if (res == 1) {
System.out.println("insert.");
} else {
System.out.println("failure.");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (ct != null) {
ct.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
java程序中来控制对数据库(表)的创建、删除、备份、恢复工作
/**
* 在java中如何使用ddl语句(create,drop,backup...)
*/
package com.db;
import java.sql.*;
public class db4 {
public static void main(String[] args) {
Connection ct = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 初始化对象
// 1.加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// 2.得到连接
ct = DriverManager.getConnection(
"jdbc:sqlserver://127.0.0.1:1433; DatabaseName=db", "sa",
"123456");
// 3.创建PrepareStatement
//创建数据库
//ps = ct.prepareStatement("create database test");
//创建表
//ps = ct.prepareStatement("create table test2(tNo int)");
//备份数据库
ps = ct.prepareStatement("backup database test to disk='C:/test.bak'");
// 执行ddl语句
boolean b = ps.execute();
if(!b){
System.out.println("ok");
}else{
System.out.println("failure");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭连接
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (ct != null) {
ct.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
实例：学生管理系统
JTable的使用
/**
* JTable的使用
*/
package com.db;
import javax.swing.*;
import java.util.*;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
public class db5 extends JFrame{
//rowData用来存放行数据
//columnNames存放列名
Vector rowData;
//columnNames存放列名
Vector columnNames;
//定义组件
JTable jt = null;
JScrollPane jsp = null;
//构造方法
public db5(){
columnNames = new Vector();
//设置列名
columnNames.add("学号");
columnNames.add("名字");
columnNames.add("性别");
columnNames.add("年龄");
columnNames.add("籍贯");
columnNames.add("系别");
rowData = new Vector();
//rowData存放多行
Vector row = new Vector();
row.add("001");
row.add("张三");
row.add("男");
row.add("20");
row.add("湖南");
row.add("软件工程");
//加入到rowData
rowData.add(row);
//创建组件
jt = new JTable(rowData, columnNames);
jsp = new JScrollPane(jt);
//添加到JFrame
add(jsp);
//设置窗体
setTitle("JTable的使用");
setSize(400, 300);
setLocationRelativeTo(null);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setVisible(true);
}
public static void main(String[] args) {
db5 gui1 = new db5();
}
}
JTable显示从数据库读取的学生信息
/**
* 从数据库读取学生信息
*/
package com.db;
import java.util.Vector;
import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import java.sql.*;
public class db6 extends JFrame {
// 定义数据库资源
Connection ct = null;
PreparedStatement ps = null;
ResultSet rs = null;
// rowData用来存放行数据
// columnNames存放列名
Vector rowData;
// columnNames存放列名
Vector columnNames;
// 定义组件
JTable jt = null;
JScrollPane jsp = null;
// 构造方法
public db6() {
columnNames = new Vector();
// 设置列名
columnNames.add("学号");
columnNames.add("名字");
columnNames.add("性别");
columnNames.add("年龄");
columnNames.add("籍贯");
columnNames.add("系别");
rowData = new Vector();
// rowData存放多行
// 从数据库取数据
try {
// 加载驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// 获取连接
ct = DriverManager.getConnection(
"jdbc:sqlserver://127.0.0.1:1433; DatabaseName=StuManage",
"sa", "123456");
// 创建PreparedStatement
ps = ct.prepareStatement("select * from stu");
// 执行查询
rs = ps.executeQuery();
// 循环获取数据
while (rs.next()) {
// rowData存放多行数据
// 定义一行数据
Vector row = new Vector();
row.add(rs.getInt("stuId"));
row.add(rs.getString("stuName"));
row.add(rs.getString("stuSex"));
row.add(rs.getInt("stuAge"));
row.add(rs.getString("stujg"));
row.add(rs.getString("stuDept"));
// 添加到rowData
rowData.add(row);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (ct != null) {
ct.close();
}
} catch (Exception e2) {
e2.printStackTrace();
}
}
// 创建组件
jt = new JTable(rowData, columnNames);
jsp = new JScrollPane(jt);
// 添加到JFrame
add(jsp);
// 设置窗体
setTitle("JTable的使用");
setSize(400, 300);
setLocationRelativeTo(null);
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
setVisible(true);
}
public static void main(String[] args) {
db6 ui = new db6();
}
}
Mini学生管理系统
--创建数据库
create database StuManage;
--使用数据库
use StuManage;
--创建学生表
create table stu
(
stuId int primary key identity(10000,1),--学号
stuName nvarchar(50) not null,--姓名
stuSex nchar(1) check(stuSex in('男','女')) default '男',--性别
stuAge int check(stuAge > 1),--年龄
stujg nvarchar(30),--籍贯
stuDept nvarchar(40)--专业
);
--插入数据
insert into stu values('张三','男',20,'湖南','软件工程');
insert into stu values('李四','男',25,'湖北','通信工程');
insert into stu values('王五','男',15,'河北','制药工程');
insert into stu values('赵丽','女',21,'湖南','英语');
--查询
select * from stu;
Model1模式
源码： https://code.csdn.net/snippets/1367849
Model2模式
源码： https://code.csdn.net/snippets/1367938
Model2模式改进
源码： https://code.csdn.net/snippets/1367979
----------参考《韩顺平.循序渐进学.java.从入门到精通》
----------参考《JDK_API_1_6_zh_CN》