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" ; |
Connection conn; |
PreparedStatement st; |
ResultSet rs; |
/** |
* 加载驱动类 |
*/ |
static { |
try { |
Class.forName(str1); |
} catch (ClassNotFoundException e) { |
// TODO Auto-generated catch block |
e.printStackTrace(); |
} |
} |
/** |
* 建立链接的方法 |
* |
* @return |
*/ |
private Connection getConnection() { |
try { |
conn = DriverManager.getConnection(url, user, password); |
} catch (Exception e) { |
// TODO: handle exception |
} |
return conn; |
} |
/** |
* 使用prepareStatement来预编译查询语句 然后传参数的值来作为条件查询数据库 返回list |
* |
* @param id |
* @return |
*/ |
public List getData(String sql, Object[] array) { |
// SQL语句 |
List list = new ArrayList(); |
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 map = new HashMap(); |
ResultSetMetaData rsmd = rs.getMetaData(); |
// 以列名为键 存储每一行数据进map |
for ( int i = 1 ; i <= rsmd.getColumnCount(); i++) { |
map.put(rsmd.getColumnName(i), rs.getObject(i)); |
} |
// 将每一个map加入list 这样list的到就是每一行 |
list.add(map); |
} |
} catch (SQLException e) { |
// TODO Auto-generated catch block |
e.printStackTrace(); |
} finally { |
// 关闭连接 |
this .close(); |
} |
return list; |
} |
/** |
* 更新数据的方法 |
* |
* @param sql |
* @param array |
* @return |
*/ |
public int update(String sql, Object array[]) { |
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 { |
System.out.println( "更新失败" ); |
} |
} catch (SQLException e) { |
e.printStackTrace(); |
} finally { |
// 关闭连接 |
this .close(); |
} |
return 0 ; |
} |
/** |
* 关闭连接 |
*/ |
private void close() { |
try { |
if (rs != null ) { |
rs.close(); |
} |
} catch (SQLException e) { |
// TODO Auto-generated catch block |
e.printStackTrace(); |
} finally { |
try { |
if (st != null ) { |
st.close(); |
} |
} catch (SQLException e) { |
// TODO Auto-generated catch block |
e.printStackTrace(); |
} finally { |
try { |
if (conn != null ) { |
conn.close(); |
} |
} catch (SQLException e) { |
// 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( "1:查询全部" ); |
System.out.println( "2:根据employeeId查询" ); |
System.out.print( "选择你要执行选项" ); |
// 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=?" ; |
Object[] array1 = { 1 }; |
List list = tc.getData(Sql1, array1); |
/** |
* 取键值 并打印 即为输出的列名 排列 |
*/ |
Map map2 = (Map) list.get( 0 ); |
// 存键值 |
Set set2 = map2.keySet(); |
Iterator it2 = set2.iterator(); |
while (it2.hasNext()) { |
System.out.print( "\\t" + it2.next()); |
} |
System.out.println(); |
//循环取出 每个行的数据 |
for (Object object : list) { |
// list里面是map对象 |
Map map = (Map) object; |
// 存键值 |
Set set = map.keySet(); |
Iterator it = set.iterator(); |
while (it.hasNext()) { |
// 取键值 |
Object key = it.next(); |
// 输出 map里的数据 |
System.out.print( "\\t " + map.get(key)); |
} |
System.out.println(); |
} |
break ; |
case 2 : |
/** |
* 根据用户输入的员工id进行查询 |
*/ |
System.out.println( "输入employeeId:" ); |
Object object = sc.nextInt(); |
Object[] array = { object }; |
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(); |
Iterator it3 = set3.iterator(); |
while (it3.hasNext()) { |
System.out.print( "\\t" + it3.next()); |
} |
System.out.println(); |
//循环输出数据 |
for (Object object2 : list2) { |
// list里面是map对象 |
Map map4 = (Map) object2; |
// 存键值 |
Set set4 = map4.keySet(); |
Iterator it4 = set4.iterator(); |
while (it4.hasNext()) { |
// 取键值 |
Object key = it4.next(); |
// 输出 map里的数据 |
System.out.print( "\\t " + map4.get(key)); |
// System.out.print("\\t"+ map.get(key)); |
} |
System.out.println(); |
} |
break ; |
} |
} |
/** |
* 更新方法 |
*/ |
public void update(){ |
System.out.print( "请输入employeeId:" ); |
Object id = sc.next(); |
System.out.print( "请输入想更新的薪水值:" ); |
Object salary = sc.next(); |
//根据用户输入的员工号来修改薪水值并判断是否执行成功 |
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:" ); |
Object id = sc.next(); |
System.out.print( "请输入employeeName:" ); |
Object name = sc.next(); |
System.out.print( "请输入email:" ); |
Object email = sc.next(); |
System.out.print( "请输入employeeSalary:" ); |
Object salary = sc.next(); |
System.out.print( "请输入dapartmentId:" ); |
Object dpId = sc.next(); |
Object[] array = {id,name,email,salary,dpId}; |
//插入用户输入的数据 并判断是否执行成功 |
String sql = "insert into employee values(?,?,?,?,?)" ; |
int line = tc.update(sql, array); |
if (line> 0 ){ |
System.out.println( "插入成功!" ); |
} |
} |
/** |
* 删除方法 |
*/ |
public void delete(){ |
System.out.print( "请输入想删除的员工号:" ); |
Object id = sc.next(); |
Object [] array = {id}; |
//删除用户输入 的员工号的数据并判断是否执行成功 |
String sql = "delete from employee where employeeId = ? " ; |
int line = tc.update(sql, array); |
if (line> 0 ){ |
System.out.println( "删除成功!" ); |
} |
} |
} |
----------------------------------------------------------------------------------------- |
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( "欢迎光临:" ); |
System.out.println( "1:查询" ); |
System.out.println( "2:更新" ); |
System.out.println( "3:插入" ); |
System.out.println( "4:删除" ); |
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 : |
//退出 |
System.out.println( "程序退出!" ); |
System.exit( 0 ); |
break ; |
} |
} |
} |
//该片段来自于http://yuncode.net |
初级程序员
by: 云代码会员 发表于:2015-12-03 17:08:36 顶(2) | 踩(2) 回复
怎么用啊
回复评论