import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class TestConnection {
/**
* 定义链接需要的字符串
*/
private static final String str1
= "com.microsoft.sqlserver.jdbc.SQLServerDriver"; private static final String url
= "jdbc:sqlserver://localhost:1433;DatabaseName=test3"; private static final String user
= "sa"; private static final String password
= "910627";
/**
* 加载驱动类
*/
static {
try {
Class.forName(str1);
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 建立链接的方法
*
* @return
*/
try {
// TODO: handle exception
}
return conn;
}
/**
* 使用prepareStatement来预编译查询语句 然后传参数的值来作为条件查询数据库 返回list
*
* @param id
* @return
*/
// SQL语句
conn = this.getConnection();
try {
// 预编译
st = conn.prepareStatement(sql);
// 利用方法传入参数
for (int i = 0; i < array.length; i++) {
st.setObject(i + 1, array[i]);
}
// 执行查询
rs = st.executeQuery();
while (rs.next()) {
// 以列名为键 存储每一行数据进map
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
map.put(rsmd.getColumnName(i), rs.getObject(i));
}
// 将每一个map加入list 这样list的到就是每一行
list.add(map);
}
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
// 关闭连接
this.close();
}
return list;
}
/**
* 更新数据的方法
*
* @param sql
* @param array
* @return
*/
conn = this.getConnection();
int line = 0;
try {
st = conn.prepareStatement(sql);
// 传参数
for (int i = 0; i < array.length; i++) {
st.setObject(i + 1, array[i]);
}
line = st.executeUpdate();
// 判断是否修改成功
if (line > 0) {
return line;
} else {
}
e.printStackTrace();
} finally {
// 关闭连接
this.close();
}
return 0;
}
/**
* 关闭连接
*/
private void close() {
try {
if (rs != null) {
rs.close();
}
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (st != null) {
st.close();
}
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (conn != null) {
conn.close();
}
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
----------------------------------------------------------------------------
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
/**
* 数据库操作类
* @author Administrator
*
*/
public class Handle {
TestConnection tc = new TestConnection();
Scanner sc
= new Scanner
(System.
in); /**
* 查询方法
*/
public void query() {
System.
out.
println("2：根据employeeId查询"); // Scanner sc2 = new Scanner(System.in);
int type2 = sc.nextInt();
switch (type2) {
case 1:
String Sql1
= "select employeeId,employeeName,email,employeeSalary,departmentId from employee where 1=?"; List list
= tc.
getData(Sql1, array1
); /**
* 取键值 并打印 即为输出的列名 排列
*/
// 存键值
Set set2
= map2.
keySet(); while (it2.hasNext()) {
System.
out.
print("\\t" + it2.
next()); }
//循环取出 每个行的数据
// list里面是map对象
// 存键值
while (it.hasNext()) {
// 取键值
// 输出 map里的数据
System.
out.
print("\\t " + map.
get(key
));
}
}
break;
case 2:
/**
* 根据用户输入的员工id进行查询
*/
System.
out.
println("输入employeeId："); String Sql2
= "select employeeId,employeeName,email,employeeSalary,departmentId from employee where employeeId =? ";
List list2
= tc.
getData(Sql2, array
); //输出列名
Map map3
= (Map) list2.
get(0); // 存键值
Set set3
= map3.
keySet();
while (it3.hasNext()) {
System.
out.
print("\\t" + it3.
next()); }
//循环输出数据
for (Object object2
: list2
) { // list里面是map对象
// 存键值
Set set4
= map4.
keySet();
while (it4.hasNext()) {
// 取键值
// 输出 map里的数据
System.
out.
print("\\t " + map4.
get(key
)); // System.out.print("\\t"+ map.get(key));
}
}
break;
}
}
/**
* 更新方法
*/
public void update(){
System.
out.
print("请输入employeeId："); System.
out.
print("请输入想更新的薪水值："); //根据用户输入的员工号来修改薪水值并判断是否执行成功
String sql
= "update employee set employeeSalary = ? where employeeId = ? " ; Object [] array
= { salary, id
}; //使用TestConnection的update方法
int line = tc.update(sql, array);
if(line>0){
System.
out.
println("信息更新成功！"); }
}
/**
* 插入方法
*/
public void insert(){
System.
out.
print("请输入employeeId："); System.
out.
print("请输入employeeName："); System.
out.
print("请输入email："); System.
out.
print("请输入employeeSalary："); System.
out.
print("请输入dapartmentId："); Object[] array
= {id,name,email,salary,dpId
}; //插入用户输入的数据 并判断是否执行成功
String sql
= "insert into employee values(?,?,?,?,?)"; int line = tc.update(sql, array);
if(line>0){
}
}
/**
* 删除方法
*/
public void delete(){
System.
out.
print("请输入想删除的员工号："); //删除用户输入 的员工号的数据并判断是否执行成功
String sql
= "delete from employee where employeeId = ? "; int line = tc.update(sql, array);
if(line>0){
}
}
}
-----------------------------------------------------------------------------------------
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.Set;
/**
* 测试类
* @author Administrator
*
*/
public class Test {
public static void main
(String[] args
) { //输出选项
System.
out.
println("5：退出"+"\\n"); System.
out.
print("你想干什么？ 请选择："); //控制台输入
Scanner sc
= new Scanner
(System.
in); //实例化数据操作类Handle
Handle hd = new Handle();
int type = sc.nextInt();
/**
* 判断用户选择操作的项
*/
switch(type){
case 1:
//调用Handle查询方法
hd.query();
break;
case 2:
//更新
hd.update();
break;
case 3:
//插入
hd.insert();
break;
case 4:
//删除
hd.delete();
break;
case 5:
//退出
break;
}
}
}
//该片段来自于http://yuncode.net