测试类代码:
package ch14.day0806.zy.zy04;
public class Start {
public static void main(String[] args) {
SubjectBiz.lists();
SubjectBiz.insert();
SubjectBiz.lists();
SubjectBiz.update();
SubjectBiz.lists();
SubjectBiz.delete();
SubjectBiz.lists();
}
}
运行结果:
SubjectId SubjectName ClassHour GradeId
1 JavaSE 100 1
2 MSSQL 20 1
5 C-- 250 3
Finished...
数据插入成功!
Finished...
SubjectId SubjectName ClassHour GradeId
1 JavaSE 100 1
2 MSSQL 20 1
5 C-- 250 3
6 C++ 250 3
Finished...
数据删除成功!
Finished...
SubjectId SubjectName ClassHour GradeId
1 JavaSE 100 1
2 MSSQL 20 1
5 C-- 250 3
6 C-- 250 3
Finished...
数据删除成功!
Finished...
SubjectId SubjectName ClassHour GradeId
1 JavaSE 100 1
2 MSSQL 20 1
Finished...
主代码:
package ch14.day0806.zy.zy04;
import java.sql.*;
public class SubjectBiz {
//MSSQL的四大金刚
static String driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
static String url="jdbc:sqlserver://127.0.0.1:61433;DatabaseName=MySchool";
static String user="sa";
static String password="sa";
/**
* 显示数据
*/
public static void lists() {
//将驱动类全名从硬盘加载到内存
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
//e.printStackTrace();
System.out.println("类全名【"+driverClassName+"】没有找到。");
}
//三大对象
Connection conn=null;
Statement st=null;
ResultSet rs=null;
//需要执行的sql语句
String sql="Select * From Subject";
//创建conn
try {
//使用驱动管理器DriverManager,创建一个连接对象conn
conn=DriverManager.getConnection(url, user, password);
//使用conn,创建语句对象st
st=conn.createStatement();
//使用st,创建结果集对象rs
rs=st.executeQuery(sql);
System.out.print("\t"+"SubjectId");
System.out.print("\t"+"SubjectName");
System.out.print("\t"+"ClassHour");
System.out.print("\t"+"GradeId");
System.out.println("");
while(rs.next()) {
System.out.print("\t"+rs.getInt("SubjectId"));
System.out.print("\t\t"+rs.getString("SubjectName"));
System.out.print("\t\t"+rs.getInt("ClassHour"));
System.out.print("\t\t"+rs.getInt("GradeId"));
System.out.println("");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
//如果conn不为null,则关闭它
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println("Finished...");
}
/**
* 插入数据
*/
public static void insert() {
//将驱动类全名从硬盘加载到内存
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
//e.printStackTrace();
System.out.println("类全名【"+driverClassName+"】没有找到。");
}
//三大对象
Connection conn=null;
Statement st=null;
ResultSet rs=null;
//数据赋值
String subName="C++";
int claHour=250;
int graId=3;
//需要执行的sql语句
String sql="insert into Subject (SubjectName,ClassHour,GradeId) "
+ "values('"+subName+"','"+claHour+"','"+graId+"')";
int result=0;
//创建conn
try {
//使用驱动管理器DriverManager,创建一个连接对象conn
conn=DriverManager.getConnection(url, user, password);
//使用conn,创建语句对象st
st=conn.createStatement();
//使用st,创建结果集对象rs
result=st.executeUpdate(sql);
System.out.println("数据插入成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("数据插入失败!");
}finally {
//如果conn不为null,则关闭它
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println("Finished...");
}
/**
* 删除数据
*/
public static void delete() {
//将驱动类全名从硬盘加载到内存
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
//e.printStackTrace();
System.out.println("类全名【"+driverClassName+"】没有找到。");
}
//三大对象
Connection conn=null;
Statement st=null;
ResultSet rs=null;
//需要执行的sql语句
String sql="delete from Subject where SubjectName='C--' ";
int result=0;
//创建conn
try {
//使用驱动管理器DriverManager,创建一个连接对象conn
conn=DriverManager.getConnection(url, user, password);
//使用conn,创建语句对象st
st=conn.createStatement();
//使用st,创建结果集对象rs
result=st.executeUpdate(sql);
System.out.println("数据删除成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("数据删除失败!");
}finally {
//如果conn不为null,则关闭它
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println("Finished...");
}
/**
* 更新数据
*/
public static void update() {
//将驱动类全名从硬盘加载到内存
try {
Class.forName(driverClassName);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
//e.printStackTrace();
System.out.println("类全名【"+driverClassName+"】没有找到。");
}
//三大对象
Connection conn=null;
Statement st=null;
ResultSet rs=null;
//数据赋值
String subName="C++";
int claHour=250;
int graId=3;
//需要执行的sql语句
String sql="update Subject set SubjectName='C--' where SubjectName='C++'";
int result=0;
//创建conn
try {
//使用驱动管理器DriverManager,创建一个连接对象conn
conn=DriverManager.getConnection(url, user, password);
//使用conn,创建语句对象st
st=conn.createStatement();
//使用st,创建结果集对象rs
result=st.executeUpdate(sql);
System.out.println("数据删除成功!");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("数据删除失败!");
}finally {
//如果conn不为null,则关闭它
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println("Finished...");
}
}