JDBC编程实践任务

实践目标：

1.使用JDBC连接SQL Server数据库
首先在navicat中创建对应数据库 所需连接的数据库为eshop_db，
加载数据库驱动：将数据库驱动文件（jar文件）拷贝到classpath路径下。

package Lx1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
              //任务三 封装后的  数据库连接与操作分离
public class DbConnerction {  //工具类
	private static final String DRIVERNAME="com.mysql.jdbc.Driver";

	private static final String URL="jdbc:mysql://localhost:3306/eshop_db";
	
	private static final String USER="root";
	
	private static final String PASSWORD="123456";
	
	
	static {
		
		try {
			Class.forName(DRIVERNAME);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
	}
	public static Connection getdata(){
		
		try {
			return DriverManager.getConnection(URL, USER, PASSWORD);
		} catch (SQLException e) {
		
			e.printStackTrace();
		}
		return null;
	}
	
	//关闭连接
	public static void close(ResultSet rs,PreparedStatement Ps,Connection connection) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (Ps!=null) {
			try {
				Ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
}

2.对英雄数据进行维护，使用PreparedState实现对英雄数据进行维护的实现思路
1、创建Hero类，该类与数据库中的t_hero表进行匹配，即t_hero表中的字段与Hero类中的属性一一匹配。

package Lx1;

public class Hero {

	private int id;
	private String num;
	private String name;
	private int output;
	private int attackPower;
	private int magicValue;
	private String type;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getNum() {
		return num;
	}
	public void setNum(String num) {
		this.num = num;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getOutput() {
		return output;
	}
	public void setOutput(int output) {
		this.output = output;
	}
	public int getAttackPower() {
		return attackPower;
	}
	public void setAttackPower(int attackPower) {
		this.attackPower = attackPower;
	}
	public int getMagicValue() {
		return magicValue;
	}
	public void setMagicValue(int magicValue) {
		this.magicValue = magicValue;
	}
	public String getType() {
		return type;
	}
	public void setType(String type) {
		this.type = type;
	}
}

3、创建HeroAccess类，在类中，分别定义新增、修改和删除三个方法实现对英雄数据的相应操作。

  //向数据库增加数据 
		public static boolean insertHero(Hero hero) {
			
		  Connection connection=null;
		  PreparedStatement  Ps=null;
		  connection=DbConnerction.getdata();
		  
		  String sql="insert into t_hero values(?,?,?,?,?,?,?)";
		  
		  try {
		  Ps=connection.prepareStatement(sql);
		  Ps.setInt(1, hero.getId());
		  Ps.setString(2, hero.getNum());
		  Ps.setString(3, hero.getName());
		  Ps.setInt(4, hero.getOutput());
		  Ps.setInt(5, hero.getAttackPower());
		  Ps.setInt(6, hero.getMagicValue());
		  Ps.setString(7, hero.getType());
		  
			return Ps.executeUpdate()>0?true:false;
		} catch (SQLException e) {
		
			e.printStackTrace();
		}finally {
		 DbConnerction.close(null, Ps, connection);
			
		}
		  
		return false;
		 
		}
		   //向数据库修改数据
		public static boolean updateHero(Hero hero) {
			Connection connection=null;
		      PreparedStatement  Ps=null;
			connection = DbConnerction.getdata();
			//3.获取PreparedStatement对象，执行sql语句
			String sql = "update t_hero set AttackPower=?,MagicValue=? where id=?";
			//预处理SQL语句
			try {
				Ps=connection.prepareStatement(sql);
					
				  Ps.setInt(1, hero.getAttackPower());		  
				  Ps.setInt(2, hero.getMagicValue());
				  Ps.setInt(3, hero.getId());
				return Ps.executeUpdate()>0?true:false;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DbConnerction.close(null, Ps, connection);
			}
			return false;
		}	
		
			//向数据删除数据
		public static boolean deleteHero(int id) {
			Connection connection=null;
			PreparedStatement Ps=null;
			connection=DbConnerction.getdata();
			//3、获取PreparedStatement对象，执行sql语句
			String sql="delete from t_hero where id=?";
			//预处理SQL语句
			try {
				Ps=connection.prepareStatement(sql);
				Ps.setInt(1, id);
				return Ps.executeUpdate()>0?true:false;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DbConnerction.close(null, Ps, connection);
				
			}
			return false;
		}

4、在main()分别测试对英雄数据进行新增、修改和删除操作。

   //添加数据
				Hero hero=new Hero();
				hero.setId(1);
				hero.setNum("007");
				hero.setName("LU CHEN HUI");
				hero.setOutput(99);
				hero.setAttackPower(66);
				hero.setMagicValue(6);
				hero.setType("NB");
				
				if(insertHero(hero)) {
					System.out.println("添加成功");
				}else {
					System.out.println("添加失败");
				}

 //修改数据
				Hero hero1=new Hero();
				hero1.setId(1);
				hero1.setNum("007");
				hero1.setName("LU CHEN HUI");
				hero1.setOutput(99);
				hero1.setAttackPower(55);//被修改值  具体详见update方法
				hero1.setMagicValue(5);//被修改值
				hero1.setType("NB");
				
				if(updateHero(hero1)) {
					System.out.println("修改成功");
				}else {
					System.out.println("修改失败");
				}
				
				break;

//删除数据
	if(deleteHero(Sc.nextInt())) {  //获取数据为我要删除id为几的数据 具体详见delete方法
			System.out.println("数据删除成功");
			}else {
				System.out.println("数据删除失败");
			}

3.遍历从数据库读取的英雄记录

		  //遍历数据库中的英雄信息
		 public static List<Hero> selectHero() {     //返回值为list集合泛型Hero类
			List<Hero> HeroList=new ArrayList<Hero>(); //创建arraylist数组
			Connection connection=null;
			PreparedStatement pstmt=null;
			ResultSet rSet=null;      //存储查询结果的对象             
			String sql="SELECT * FROM t_hero";    
			connection=DbConnerction.getdata(); 
			try {
				pstmt=connection.prepareStatement(sql);
				rSet=pstmt.executeQuery(); //executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用
				
				//判断结果集rset中是否有数据
				while (rSet.next()) {   //循环取值  
					Hero hero=new Hero();
					hero.setId(rSet.getInt("id"));
					hero.setNum(rSet.getString("num"));
					hero.setName(rSet.getString("name"));
					hero.setOutput(rSet.getInt("output"));
					hero.setAttackPower(rSet.getInt("attackPower"));
					hero.setMagicValue(rSet.getInt("magicValue"));
					hero.setType(rSet.getString("type"));
					HeroList.add(hero);
				}
				
				return HeroList;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				DbConnerction.close(rSet, pstmt, connection);
			}
			
			return null;
		}

main方法：

List<Hero> heroList = selectHero(); //把方法返回的对象给新数组用来取值遍历
				if (heroList == null) {
					System.out.println("没有数据");
				} else {
					for (int i = 0; i < heroList.size(); i++) {
						Hero hero2 = heroList.get(i);
						System.out.println("英雄序号" + "\t" + "英雄编号" + "\t" + "英雄姓名"+ "\t"
								+ "血量" + "\t" + "攻击值"+"\t"+"魔法值"+"\t"+"类型");
						System.out.println(hero2.getId() + "\t" + hero2.getNum() + "\t" + hero2.getName() + "\t"
								+ hero2.getOutput() + "\t" + hero2.getAttackPower()+"\t"+hero2.getMagicValue()+"\t"+hero2.getType());
					}
				}