【上机作业】【java】2010.4.27 SQL数据库编程

这次主要是针对执行动态SQL语句的联系 prepareStatement

数据源名称：stu
默认数据库：student
驱动程序 ：SQL Server

(1)查询
import java.sql.*;
class findstudent
{ Connection cn;
PreparedStatement ps;
ResultSet rs;
public findstudent()
{
try{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
String url=”jdbc:odbc:stu” ;cn=DriverManager.getConnection(url,”sa”,””);
String sql=”select * from stu where sex=?”;
ps=cn.prepareStatement(sql);
ps.setString(1,”m”);
rs=ps.executeQuery();
System.out.print(“学号t”);
System.out.print(“姓名t”);
System.out.print(“性别t”);
System.out.println(“年龄”);
while(rs.next())
{
System.out.print(rs.getString(“id”)+”t”);
System.out.print(rs.getString(“name”)+”t”);
System.out.print(rs.getString(“sex”)+”t”);
System.out.println(rs.getInt(“age”));
}
}
catch(Exception e)
{ e.printStackTrace();
}
}
public static void main(String []args){
findstudent f=new findstudent();
}
}

(2)添加
import java.sql.*;
class add
{ Connection cn;
PreparedStatement ps;
ResultSet rs;
public add()
{
try{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
String url=”jdbc:odbc:stu” ;cn=DriverManager.getConnection(url,”sa”,””);
String sql=”insert into stu values(?,?,?,?)”;
ps=cn.prepareStatement(sql);
ps.setString(1,”1002″);
ps.setString(2,”测试2″);
ps.setString(3,”m”);
ps.setString(4,”21″);

int i=ps.executeUpdate();
if(i>0)
{System.out.println(“add ok”);
}
}
catch(Exception e)
{ e.printStackTrace();
}
}
public static void main(String []args){
add f=new add();
}
}

(3)删除
import java.util.Scanner;
import java.sql.*;
class del
{ Connection cn;
PreparedStatement ps;
ResultSet rs;
public del()
{
try{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
String url=”jdbc:odbc:stu” ;cn=DriverManager.getConnection(url,”sa”,””);
String sql=”delete from stu where id=?”;
ps=cn.prepareStatement(sql);
System.out.print(“输入学号:”);
Scanner s=new Scanner(System.in);
ps.setString(1,s.next());
int i=ps.executeUpdate();
if(i>0)
{System.out.println(“del ok”);
}
}
catch(Exception e)
{ e.printStackTrace();
}
}
public static void main(String []args){
del f=new del();
}
}

(4)修改
import java.util.Scanner;
import java.sql.*;
class update
{ Connection cn;
PreparedStatement ps;
ResultSet rs;
public update()
{
try{
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
String url=”jdbc:odbc:stu” ;cn=DriverManager.getConnection(url,”sa”,””);
String sql=”update stu set name=? where id=?”;
ps=cn.prepareStatement(sql);
System.out.print(“输入学号:”);
Scanner s=new Scanner(System.in);
ps.setString(2,s.next());
System.out.print(“输入姓名:”);
ps.setString(1,s.next());
int i=ps.executeUpdate();
if(i>0)
{System.out.println(“update ok”);
}
}
catch(Exception e)
{ e.printStackTrace();
}
}
public static void main(String []args){
update u=new update();
}
}