public class Test1 {
public static void main(String[] args) {
// TODO 自动生成的方法存根
Server_1 s1=new Server_1();
s1.server();
/*Server_2 s2=new Server_1();
s2.server();*/
}
}
//jdbc 取
class Server_1{
public void server() {
PreparedStatement ps=null;
Connection ct=null;
ResultSet rs=null;
try{
// 加载驱动(作用是把需要的驱动程序加入内存)
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// 得到连接(数据源,用户名,密码)
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=张戴峰;","sa","zhangdaifeng");
// 创建Statemet 和 preparedStatemet (区别)
/*ps=ct.prepareStatement("select * from family");//主要用于取出数据*/
ps=ct.prepareStatement("select name 姓名,age 年龄,title 称号,number 战斗力 from family,force where family.grade=force.grade order by 年龄");
// 执行:增加,删除,修改,使用executeUdate
// 查询,使用executeQuery
rs=ps.executeQuery();
// int i=ps.executeUpdate("delete from family where name='无名'");
while(rs.next()) {
/*
String name=rs.getString(1);//有序
// String name=rs.getString("name");//无序
int age=rs.getInt(2);
System.out.println("姓名="+name+" "+"年龄="+age);*/
String name=rs.getString("姓名");
int age=rs.getInt(2);
String title=rs.getString("称号");
int number=rs.getShort("战斗力");
System.out.println("姓名:"+name+",年龄"+age+"岁,号称"+title+",战斗力"+number+"万。");
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
try {
if(ps!=null)
ps.close();
if(ct!=null)
ct.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
}
//jdbc 存
class Server_2{
public void server() {
Connection ct=null;
java.sql.Statement sm=null;
try{
// 加载驱动(作用是把需要的驱动程序加入内存)
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// 得到连接(数据源,用户名,密码)
ct=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=张戴峰;","sa","zhangdaifeng");
// 创建Statemet 和 PreparedStatemet (区别)
sm=ct.createStatement();//主要用于,发送sql语句到数据库
// 执行
// int i=sm.executeUpdate("insert into family values('无名',80,'男','1000-1-1')");
int i=sm.executeUpdate("delete from family where name='无名'");
if(i==0) {
System.out.println("失败");
}else {
System.out.println("成功");
}
}catch (Exception e) {
// TODO: handle exception
}finally {
try {
if(sm!=null)
sm.close();
if(ct!=null)
ct.close();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
}
create table family(
name varchar(10) not null,
age int not null,
sex varchar(4) not null check (sex in ('男','女')) default('男'),
brith char(10) not null,
)
create table force(
grade int not null default(0),
title varchar(4) not null default('武童'),
number char(10) not null default(0),
)
drop table force;
drop table family;
select * from family;
select * from force;
insert into family values('张戴峰',22,'男','1998-1-8');
update family set grade=2 where name='张戴峰';
insert into family values('张峻峰',16,'男','2003-10-1');
update family set grade=1 where name='张峻峰';
insert into family values('张玉堂',47,'男','1971-5-8');
update family set grade=3 where name='张玉堂';
insert into family values('吴小伍',45,'女','1974-8-5');
update family set grade=4 where name='吴小伍';
insert into force values(1,'武童',6);
insert into force values(2,'武者',66);
insert into force values(3,'武师',666);
insert into force values(4,'武将',6666);
insert into force values(5,'武尊',66666);
insert into force values(6,'武帝',666666);
insert into force values(7,'武神',6666666);
delete from family where brith='2000-0-0';
alter table family add grade int not null default(0);
select name 姓名,age 年龄,title 称号,number 战斗力 from family,force where family.grade=force.grade order by 年龄;