Java的数据库连接
1、数据库的连接
（1）select 查询操作：
执行语句：ResultSet executeQuery(sql);
处理结果集：rs.next();rs.getXxx();
释放资源：ResultSet.close();
（2）预处理对象 PreparedStatement：解决SQL注入问题
sql语句：？ 占位符；
获得执行对象：prepareStatement(sql)；
执行语句：setXxx(int, Obj);executeQuery()；
释放资源：PreparedStatement.close();
举个例子：
package com.oracle.difficult; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Scanner; public class demo04 { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1、注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2、获得连接 String url = "jdbc:mysql://localhost:3306/ceshi?characterEncoding=gbk"; String root = "root"; String password = "123"; Connection con = DriverManager.getConnection(url, root, password); //3、获得语句执行平台 Statement sta = con.createStatement(); //4、执行sql语句 Scanner sc = new Scanner(System.in); System.out.println("请输入用户名："); String user = sc.next(); System.out.println("请输入密码："); String pass = sc.next(); String sql = "select count(*) from user where uname = '"+user+"' "+"and pwd = '"+pass+"'"; //需要导包 import java.sql.ResultSet; ResultSet rs = sta.executeQuery(sql); /*//3、获得语句执行平台（预处理对象，解决SQL注入问题） String sql = "select count(*) from ceshi1 where id = ? and sname = ?"; PreparedStatement past = con.prepareStatement(sql); //4、执行SQL语句 Scanner sc = new Scanner(System.in); System.out.println("请输入用户名："); int user = sc.nextInt(); System.out.println("请输入密码："); String pass = sc.next(); past.setInt(1, user); past.setString(2, pass); ResultSet rs = past.executeQuery();*/ //5、处理结果集 int count = 0; while(rs.next()){ count = rs.getInt(1); } if(count > 0){ System.out.println("登录成功！！"); }else{ System.out.println("登录失败"); } //6、释放资源 rs.close(); sta.close(); con.close(); } }
SQL语句注入问题：
（3）JDBCUtils工具类：
将注册驱动、获得连接和释放资源（方法重载）封装起来；
package com.oracle.tools; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCUtils { public static Connection get() { //1、注册驱动 try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } //2、获得连接 String url = "jdbc:mysql://localhost:3306/ceshi?characterEncoding=gbk"; String root = "root"; String pwd = "123"; Connection conn = null; try { conn = DriverManager.getConnection(url,root,pwd); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static void close(Connection conn,PreparedStatement pst){ if(pst != null){ try { pst.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(Connection conn,PreparedStatement pst,ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(pst != null){ try { pst.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
（4）项目分层（分包）
view层：视图层，项目中的界面；
controller层：控制层，获取界面上的数据；将要实现的功能交给业务层处理；
service层作用: 业务层, 功能的实现, 与controller控制层和数据访问层DAO交互, 将对数据库的操作交给DAO数据访问层来处理
dao层作用: 数据访问层, 用来操作数据库表的数据
db数据库: 这里指MySQL
domain 实体包: 存放JavaBean
tools工具包:存放项目中使用到的工具类
test测试包: 存放项目功能测试的代码
各举一个例子：
view层：
package com.oracle.view; import java.util.List; import java.util.Scanner; import com.oracle.controller.Sortcontroller; import com.oracle.controller.UserController; import com.oracle.domain.Sort; public class Mainview { private UserController userController = new UserController(); private Sortcontroller sortcontroller = new Sortcontroller(); public void mainShow(){ System.out.println("--------欢迎进入商城--------"); System.out.println("1、登陆"); System.out.println("2、注册"); System.out.println("3、退出"); System.out.println("请输入你的选择"); } public void IndexShow(){ System.out.println("-------------首页-------------"); System.out.println("1、新增分类"); System.out.println("2、修改分类"); System.out.println("3、删除分类"); System.out.println("4、查询所有分类"); System.out.println("5、返回上级菜单"); System.out.println("请输入你的选择"); } //登录页面 public void login(){ System.out.println("-----------登陆页面-------------"); System.out.println("请输入用户名："); Scanner sc = new Scanner(System.in); String username = sc.next(); System.out.println("请输入密码："); String password = sc.next(); //调用Controller 层的登陆方法 int count = userController.login(username, password); if(count > 0){ System.out.println("登陆成功！！"); run2(); }else{ System.out.println("登陆失败"); } } //注册页面 public void register(){ System.out.println("-----------注册页面-------------"); System.out.println("请输入用户名："); Scanner sc = new Scanner(System.in); String username = sc.next(); System.out.println("请输入密码："); String password = sc.next(); //调用Controller 层的注册方法 System.out.println(userController.registerUser(username, password)); } //一级菜单 public void run(){ Scanner sc = new Scanner(System.in); while(true){ mainShow(); int choose = sc.nextInt(); switch(choose){ case 1: //登陆 login(); break; case 2: register(); break; case 3: return; } } } //二级菜单 public void run2(){ Scanner sc = new Scanner(System.in); while(true){ IndexShow(); int choose = sc.nextInt(); switch(choose){ case 1: add(); break; case 2: update(); break; case 3: break; case 4: getAll(); break; case 5: return; } } } //新增分类页面 public void add(){ Scanner sc = new Scanner(System.in); System.out.println("--------------新增页面------------"); System.out.println("请输入你要新增的分类名称"); String sname = sc.next(); //调用controller新增的方法 System.out.println(sortcontroller.addSort(sname)); } //查询所有分类页面 public void getAll(){ System.out.println("-------------查询所有分类-------------"); //调用controller的查询方法 List<Sort> list = sortcontroller.getAll(); System.out.println("分类编号\t分类名称\t"); for(Sort s:list){ System.out.println(s.getSid()+"\t"+s.getSname()); } } //修改分类页面 public void update(){ getAll(); System.out.println("请输入要您要修改的分类编号"); Scanner sc = new Scanner(System.in); int id = sc.nextInt(); System.out.println("请输入你要修改的分类名称"); String sname = sc.next(); //调用Controller的修改方法 String mes = sortcontroller.updateSort(id, sname); System.out.println(mes); } }
controller层：
package com.oracle.controller; import com.oracle.domain.User; import com.oracle.service.UserService; public class UserController { //封装前台数据传给后台 //接收后台数据传给后台 private UserService userService = new UserService(); public int login(String username,String password){ return userService.login(username, password); } public String registerUser(String username,String password){ return userService.registerUser(username, password); } /*public String register(String uname,String pwd){ User user = new User(); }*/ }
service层：
package com.oracle.service; import java.sql.SQLException; import com.oracle.dao.Userdao; import com.oracle.domain.User; public class UserService { //UserService层只能调用Dao层方法（相对） //view层调用controller层调用service层调用dao层 private Userdao userDao = new Userdao(); public int login(String username,String password){ int count = 0; try { count = userDao.login(username, password); } catch (SQLException e) { e.printStackTrace(); } return count; } //注册方法 public String register(User user){ int row = 0; String mes = ""; try { row = userDao.register(user); } catch (SQLException e) { e.printStackTrace(); } if(row > 0){ mes = "注册成功"; }else{ mes = "注册失败"; } return mes; } //----------- public String registerUser(String username,String password){ int count = 0; String mes =""; try { count = userDao.registerUser(username, password); } catch (SQLException e) { e.printStackTrace(); } if(count == 0){ int row = 0; try { row =userDao.updateUser(username, password); } catch (SQLException e) { e.printStackTrace(); } if(row > 0){ mes = "注册成功！"; }else{ mes = "注册失败！"; } }else{ mes = "用户名已存在！"; } return mes; } }
dao层：
package com.oracle.dao; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.oracle.domain.User; import com.oracle.tools.JDBCUtils; public class Userdao { //登陆验证用户名或密码是否正确 public int login(String username,String password) throws SQLException{ Connection conn = JDBCUtils.get(); String sql = "select count(*) from user where uname = ? and pwd = ?"; PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, username); pst.setString(2, password); ResultSet rs = pst.executeQuery(); int count = 0; while(rs.next()){ count=rs.getInt(1); } return count; } //注册方法 //1注册用户 public int registerUser(String username,String password) throws SQLException{ Connection conn = JDBCUtils.get(); String sql = "select count(*) from user where uname = ? "; PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, username); ResultSet rs = pst.executeQuery(); int count = 0; while(rs.next()){ count=rs.getInt(1); } JDBCUtils.close(conn, pst, rs); return count; } //2添加用户 public int updateUser(String username,String password) throws SQLException{ Connection conn = JDBCUtils.get(); String sql = "insert into user (uname,pwd) values (?,?)"; PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, username); pst.setString(2, password); int row = pst.executeUpdate(); JDBCUtils.close(conn, pst); return row; } public int register(User user) throws SQLException{ Connection conn = JDBCUtils.get(); String sql = "insert into user(uname,pwd) values(?,?)"; PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, user.getUsername()); pst.setString(2, user.getPassword()); int row = pst.executeUpdate(); JDBCUtils.close(conn, pst); return row; } }
domain层：
package com.oracle.domain; public class User { private int uid; private String username; private String password; public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } public String toString() { return "User [uid=" + uid + ", username=" + username + ", password=" + password + "]"; } public User() { super(); } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } }
tools层：
package com.oracle.tools; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class JDBCUtils { public static Connection get() { //1、注册驱动 try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } //2、获得连接 String url = "jdbc:mysql://localhost:3306/ceshi?characterEncoding=gbk"; String root = "root"; String pwd = "123"; Connection conn = null; try { conn = DriverManager.getConnection(url,root,pwd); } catch (SQLException e) { e.printStackTrace(); } return conn; } public static void close(Connection conn,PreparedStatement pst){ if(pst != null){ try { pst.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(Connection conn,PreparedStatement pst,ResultSet rs){ if(rs != null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(pst != null){ try { pst.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
test层：
package com.oracle.test; import com.oracle.view.Mainview; public class test { public static void main(String[] args) { new Mainview().run(); } }
