“商苑面馆:Java命令行版之 Dao层(MySQL实现)”的版本间的差异
来自CloudWiki
(→编写测试代码) |
|||
(未显示2个用户的10个中间版本) | |||
第3行: | 第3行: | ||
==前期准备== | ==前期准备== | ||
− | + | ===在entity包下建立User类=== | |
<nowiki>package entity; | <nowiki>package entity; | ||
第49行: | 第49行: | ||
} | } | ||
}</nowiki> | }</nowiki> | ||
+ | |||
+ | ===创建测试数据=== | ||
+ | |||
+ | 接下来我们在 MySQL 中创建 cloud 数据库,并创建 user 数据表,表结构如下: | ||
+ | |||
+ | <nowiki>mysql> create database cloud; | ||
+ | |||
+ | Query OK, 1 row affected (0.00 sec) | ||
+ | |||
+ | mysql> use cloud; | ||
+ | Database changed | ||
+ | mysql> CREATE TABLE IF NOT EXISTS `user`( | ||
+ | `id` INT UNSIGNED AUTO_INCREMENT, | ||
+ | `name` VARCHAR(100) NOT NULL, | ||
+ | `password` VARCHAR(40) NOT NULL, | ||
+ | PRIMARY KEY ( `id` ) | ||
+ | )ENGINE=InnoDB DEFAULT CHARSET=utf8; | ||
+ | mysql> CREATE UNIQUE INDEX uname on user(name); | ||
+ | Query OK, 0 rows affected (0.25 sec) | ||
+ | Records: 0 Duplicates: 0 Warnings: 0 | ||
+ | </nowiki> | ||
+ | |||
+ | 插入一些数据: | ||
+ | |||
+ | <nowiki>mysql> INSERT INTO `user` (name,password) VALUES ('ma', '123456'); | ||
+ | Query OK, 1 row affected (0.12 sec) | ||
+ | |||
+ | mysql> INSERT INTO `user` (name,password) VALUES ('ma', '123457'); | ||
+ | ERROR 1062 (23000): Duplicate entry 'ma' for key 2</nowiki> | ||
+ | |||
+ | </nowiki> | ||
+ | ===Eclipse导入数据库驱动程序=== | ||
+ | *[[Eclipse导入数据库驱动程序]] | ||
+ | |||
==实现步骤== | ==实现步骤== | ||
第69行: | 第103行: | ||
}</nowiki> | }</nowiki> | ||
+ | *我们之前已经定义了一个UserDao接口。 | ||
+ | *现在我们用MYSQL数据库来实现这个接口。 | ||
===建立接口的实现类=== | ===建立接口的实现类=== | ||
接口实现类可以用多种方式实现,ArrayList,LinkedList, MySQL等 | 接口实现类可以用多种方式实现,ArrayList,LinkedList, MySQL等 | ||
第74行: | 第110行: | ||
这里我们用MySQL实现 | 这里我们用MySQL实现 | ||
− | ==== | + | ====建立接口实现类UserDaoSQL==== |
<nowiki>package dao; | <nowiki>package dao; | ||
第85行: | 第121行: | ||
import entity.User; | import entity.User; | ||
− | public class UserDaoSQL | + | public class UserDaoSQL { |
Connection con=null; | Connection con=null; | ||
public UserDaoSQL(){ | public UserDaoSQL(){ | ||
第100行: | 第136行: | ||
</nowiki> | </nowiki> | ||
+ | |||
====编写连接方法getConn==== | ====编写连接方法getConn==== | ||
这个方法的作用主要是进行数据库的初始连接: | 这个方法的作用主要是进行数据库的初始连接: | ||
第125行: | 第162行: | ||
}</nowiki> | }</nowiki> | ||
− | ==== | + | ====登录验证方法:verify()==== |
<nowiki> | <nowiki> | ||
− | + | public boolean verify(String username, String password) { | |
− | + | try { | |
− | Connection con = this.con; | + | Connection con = this.con;//建立数据库的连接 |
− | + | ResultSet rs = null; //建立一个空的结果集 | |
− | ResultSet rs = null; | + | |
− | + | //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; | |
− | String sql = " | + | String sql = "select * from user where name=? and password=?"; |
− | + | PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql); | |
− | + | ||
− | if(rs.next()) { | + | |
+ | //使用2. setXXX()系列方法为sql命令设置值 | ||
+ | pstmt.setString(1, username);// | ||
+ | pstmt.setString(2, password); //setInt,setFloat | ||
+ | |||
+ | //3.执行查询 | ||
+ | rs = pstmt.executeQuery();// | ||
+ | |||
+ | if(rs.next()) { | ||
System.out.println("欢迎" + rs.getString("name")); | System.out.println("欢迎" + rs.getString("name")); | ||
− | + | pstmt.close(); | |
+ | return true; | ||
} else { | } else { | ||
− | System.out.println("用户名或密码错误!"); | + | System.out.println("用户名或密码错误!"); |
+ | pstmt.close(); | ||
+ | return false; | ||
} | } | ||
− | } catch (SQLException e) { | + | |
+ | |||
+ | }catch (SQLException e) { | ||
System.out.println(e.getMessage()); | System.out.println(e.getMessage()); | ||
− | + | } | |
return false; | return false; | ||
}</nowiki> | }</nowiki> | ||
− | |||
在UserDaoSQL类的main方法中编写测试代码: | 在UserDaoSQL类的main方法中编写测试代码: | ||
<nowiki> | <nowiki> | ||
− | + | public static void main(String[] args) throws Exception{ | |
− | userset.verify("ma","123456"); | + | // TODO Auto-generated method stub |
− | </nowiki> | + | |
+ | User u1 = new User("ma","123456"); | ||
+ | UserDaoSQL userset =new UserDaoSQL(); | ||
+ | userset.verify(u1.getName(),u1.getPwd()); | ||
+ | userset.con.close();//关闭数据库 | ||
+ | }</nowiki> | ||
+ | |||
+ | [[文件:java7-40.png]] | ||
+ | |||
+ | ====添加用户方法:addUser( )==== | ||
+ | <nowiki>public int addUser(User user){ | ||
+ | Connection conn = this.con;//建立数据库的连接 | ||
+ | int i = 0; //建立一个空的整形变量i | ||
+ | |||
+ | try { | ||
+ | //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; | ||
+ | String sql = "insert into user (name,password) values(?,?)"; | ||
+ | PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql); | ||
+ | |||
+ | //使用2. setXXX()系列方法为sql命令设置值 | ||
+ | pstmt.setString(1, user.getName()); | ||
+ | pstmt.setString(2, user.getPassword()); //pstmt.setInt(2, user.getAge()); pstmt.setDate | ||
+ | |||
+ | //3.执行查询 | ||
+ | i = pstmt.executeUpdate(); | ||
+ | if(i!=0){ System.out.println(user.getName()+"插入成功!"); | ||
+ | }else{ | ||
+ | System.out.println(user.getName()+"插入失败!"); | ||
+ | } | ||
+ | pstmt.close(); | ||
+ | |||
+ | } catch (SQLException e) {//检测/捕获异常 | ||
+ | System.out.println(e.getMessage()); | ||
+ | } | ||
+ | return i; | ||
+ | }</nowiki> | ||
+ | |||
+ | 在UserDaoSQL类的main方法中编写测试代码: | ||
+ | |||
+ | <nowiki>public static void main(String[] args) throws Exception{ | ||
+ | // TODO Auto-generated method stub | ||
+ | |||
+ | User u1 = new User("ma","123456"); | ||
+ | UserDaoSQL userset =new UserDaoSQL(); | ||
+ | userset.verify(u1.getName(),u1.getPwd()); | ||
+ | User u2 = new User("xing5","123456"); | ||
+ | userset.addUser(u2);//插入数据 | ||
+ | userset.con.close();//关闭数据库 | ||
+ | }</nowiki> | ||
+ | |||
+ | [[文件:java7-41.png]] | ||
+ | |||
+ | ====搜索用户方法:search()==== | ||
+ | <nowiki> public User search(String s){ | ||
+ | try { | ||
+ | Connection con = this.con;//建立数据库的连接 | ||
+ | ResultSet rs = null; //建立一个空的结果集 | ||
+ | |||
+ | //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; | ||
+ | String sql = "select * from user where name=?"; | ||
+ | PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql); | ||
+ | |||
+ | |||
+ | //使用2. setXXX()系列方法为sql命令设置值 | ||
+ | pstmt.setString(1, s);// | ||
+ | |||
+ | //3.执行查询 | ||
+ | rs = pstmt.executeQuery();//执行查询 | ||
+ | int col = rs.getMetaData().getColumnCount();//获取字段数 | ||
+ | |||
+ | |||
+ | //4. 打印数据表表头 | ||
+ | System.out.println("============================"); | ||
+ | for (int i = 1; i <= col; i++) { | ||
+ | String col_name = rs.getMetaData().getColumnName(i); | ||
+ | System.out.print(col_name+"\t"); | ||
+ | } | ||
+ | System.out.println(""); | ||
+ | System.out.println("----------------------------"); | ||
+ | |||
+ | |||
+ | //5. 打印查询到的记录 | ||
+ | User u1 =null; | ||
+ | while (rs.next()) { | ||
+ | |||
+ | for (int i = 1; i <= col; i++) { | ||
+ | System.out.print(rs.getString(i) + "\t"); | ||
+ | |||
+ | } | ||
+ | System.out.println(""); | ||
+ | u1 = new User(rs.getString(2),rs.getString(3)); | ||
+ | |||
+ | } | ||
+ | System.out.println("============================"); | ||
+ | pstmt.close(); | ||
+ | return u1; | ||
+ | |||
+ | }catch (SQLException e) {//捕捉、处理异常 | ||
+ | System.out.println(e.getMessage()); | ||
+ | } | ||
+ | return null; | ||
+ | |||
+ | }</nowiki> | ||
+ | |||
+ | 在UserDaoSQL类的main方法中编写测试代码: | ||
+ | |||
+ | <nowiki>public static void main(String[] args) throws Exception{ | ||
+ | // TODO Auto-generated method stub | ||
+ | |||
+ | User u1 = new User("ma","123456"); | ||
+ | UserDaoSQL userset =new UserDaoSQL(); | ||
+ | userset.verify(u1.getName(),u1.getPwd()); | ||
+ | User u2 = new User("xing6","123456"); | ||
+ | userset.addUser(u2);//插入数据 | ||
+ | User u3 = userset.search("xing6");//查询数据 | ||
+ | System.out.println("名称为xing6的用户资料如下:"); | ||
+ | System.out.println(u3.toString()); | ||
+ | userset.con.close();//关闭数据库 | ||
+ | }</nowiki> | ||
+ | |||
+ | [[文件:java7-42.png]] | ||
+ | |||
+ | ====列出全部记录:toString()==== | ||
+ | <nowiki>public String toString(){ | ||
+ | try { | ||
+ | |||
+ | Connection con = this.con;//建立数据库的连接 | ||
+ | ResultSet rs = null; //建立一个空的结果集 | ||
+ | String s="";//返回值 | ||
+ | |||
+ | //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; | ||
+ | String sql = "select * from user"; | ||
+ | PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql); | ||
+ | |||
+ | //2.执行查询 | ||
+ | rs = pstmt.executeQuery();//执行查询 | ||
+ | int col = rs.getMetaData().getColumnCount();//获取字段数 | ||
+ | |||
+ | |||
+ | //4. 打印数据表表头 | ||
+ | System.out.println("============================"); | ||
+ | for (int i = 1; i <= col; i++) { | ||
+ | String col_name = rs.getMetaData().getColumnName(i);//获取字段名称 | ||
+ | System.out.print(col_name+"\t"); | ||
+ | } | ||
+ | System.out.println(""); | ||
+ | System.out.println("----------------------------"); | ||
+ | |||
+ | |||
+ | //5. 打印查询到的记录 | ||
+ | User u1 =null; | ||
+ | while (rs.next()) { | ||
+ | |||
+ | for (int i = 1; i <= col; i++) { | ||
+ | System.out.print(rs.getString(i) + "\t"); | ||
+ | |||
+ | } | ||
+ | System.out.println(""); | ||
+ | u1 = new User(rs.getString(2),rs.getString(3)); | ||
+ | s += u1.toString()+"\r\n"; | ||
+ | |||
+ | } | ||
+ | System.out.println("============================"); | ||
+ | pstmt.close(); | ||
+ | return s; | ||
+ | |||
+ | }catch (SQLException e) {//捕捉、处理异常 | ||
+ | System.out.println(e.getMessage()); | ||
+ | } | ||
+ | return null; | ||
+ | |||
+ | }</nowiki> | ||
+ | |||
+ | 在UserDaoSQL类的main方法中编写测试代码: | ||
+ | |||
+ | <nowiki>public static void main(String[] args) throws Exception{ | ||
+ | // TODO Auto-generated method stub | ||
+ | |||
+ | User u1 = new User("ma","123456"); | ||
+ | UserDaoSQL userset =new UserDaoSQL(); | ||
+ | userset.verify(u1.getName(),u1.getPwd()); | ||
+ | User u2 = new User("xing6","123456"); | ||
+ | userset.addUser(u2);//插入数据 | ||
+ | User u3 = userset.search("xing6");//查询数据 | ||
+ | System.out.println("名称为xing6的用户资料如下:"); | ||
+ | System.out.println(u3.toString()); | ||
+ | System.out.println("全站所有用户如下:"); | ||
+ | String s= userset.toString(); | ||
+ | if(s!=null){ | ||
+ | System.out.println(s); | ||
+ | }else{ | ||
+ | System.out.println("无符合条件的记录!"); | ||
+ | } | ||
+ | userset.con.close();//关闭数据库 | ||
+ | } | ||
+ | </nowiki> | ||
+ | |||
+ | [[文件:java7-43.png]] | ||
+ | ===实现接口=== | ||
+ | 到现在为止,UserDaoSQL已经实现了UserDao接口的全部方法, | ||
+ | |||
+ | 因此可以在类前做如下标识: | ||
− | [[文件:java7- | + | [[文件:java7-44.png]] |
2019年5月19日 (日) 06:14的最新版本
目录
Dao层
前期准备
在entity包下建立User类
package entity; public class User { //成员变量 public String name; public String password; //构造方法 public User(){ } public User(String name,String password ){ this.name=name; this.password=password; } //读方法 public String getname(){ return this.name; } //写方法 public boolean setName(String s){ this.name = s; return true; } //获取用户密码 public String getPwd(){ return this.password; } //写方法 public boolean setPwd(String p){ this.password = p; return true; } }
创建测试数据
接下来我们在 MySQL 中创建 cloud 数据库,并创建 user 数据表,表结构如下:
mysql> create database cloud; Query OK, 1 row affected (0.00 sec) mysql> use cloud; Database changed mysql> CREATE TABLE IF NOT EXISTS `user`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `password` VARCHAR(40) NOT NULL, PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> CREATE UNIQUE INDEX uname on user(name); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0
插入一些数据:
mysql> INSERT INTO `user` (name,password) VALUES ('ma', '123456'); Query OK, 1 row affected (0.12 sec) mysql> INSERT INTO `user` (name,password) VALUES ('ma', '123457'); ERROR 1062 (23000): Duplicate entry 'ma' for key 2
</nowiki>
Eclipse导入数据库驱动程序
实现步骤
建立接口UserDao
在包dao下建立抽象接口UserDao
package dao; import entity.User; public interface UserDao { public boolean addUser(User u);//抽象方法1:增加用户,接口的实现类必须实现这个方法 public boolean verify(String name, String pwd);//验证用户名和密码是否一致 public User search(String s);//抽象方法2:搜索商品,接口的实现类必须实现这个方法 public String toString(); //抽象方法3:展示所有商品,接口的实现类必须实现这个方法 }
- 我们之前已经定义了一个UserDao接口。
- 现在我们用MYSQL数据库来实现这个接口。
建立接口的实现类
接口实现类可以用多种方式实现,ArrayList,LinkedList, MySQL等
这里我们用MySQL实现
建立接口实现类UserDaoSQL
package dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.*; import entity.User; public class UserDaoSQL { Connection con=null; public UserDaoSQL(){ con = getConn(); } public static void main(String[] args) { // TODO Auto-generated method stub } }
编写连接方法getConn
这个方法的作用主要是进行数据库的初始连接:
private static Connection getConn() { // JDBC 驱动名及数据库 URL final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; final String DB_URL = "jdbc:mysql://localhost:3306/cloud"; // 数据库的用户名与密码,需要根据自己的设置 final String USER = "root"; final String PASS = "000000"; Connection conn = null; try { Class.forName(JDBC_DRIVER); //classLoader,加载对应驱动 conn = (Connection) DriverManager.getConnection(DB_URL, USER, PASS); System.out.println("MySQL连接成功!"); } catch (ClassNotFoundException e) { System.out.println(e.getMessage()); } catch (SQLException e) { System.out.println(e.getMessage()); } return conn; }
登录验证方法:verify()
public boolean verify(String username, String password) { try { Connection con = this.con;//建立数据库的连接 ResultSet rs = null; //建立一个空的结果集 //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; String sql = "select * from user where name=? and password=?"; PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql); //使用2. setXXX()系列方法为sql命令设置值 pstmt.setString(1, username);// pstmt.setString(2, password); //setInt,setFloat //3.执行查询 rs = pstmt.executeQuery();// if(rs.next()) { System.out.println("欢迎" + rs.getString("name")); pstmt.close(); return true; } else { System.out.println("用户名或密码错误!"); pstmt.close(); return false; } }catch (SQLException e) { System.out.println(e.getMessage()); } return false; }
在UserDaoSQL类的main方法中编写测试代码:
public static void main(String[] args) throws Exception{ // TODO Auto-generated method stub User u1 = new User("ma","123456"); UserDaoSQL userset =new UserDaoSQL(); userset.verify(u1.getName(),u1.getPwd()); userset.con.close();//关闭数据库 }
添加用户方法:addUser( )
public int addUser(User user){ Connection conn = this.con;//建立数据库的连接 int i = 0; //建立一个空的整形变量i try { //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; String sql = "insert into user (name,password) values(?,?)"; PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(sql); //使用2. setXXX()系列方法为sql命令设置值 pstmt.setString(1, user.getName()); pstmt.setString(2, user.getPassword()); //pstmt.setInt(2, user.getAge()); pstmt.setDate //3.执行查询 i = pstmt.executeUpdate(); if(i!=0){ System.out.println(user.getName()+"插入成功!"); }else{ System.out.println(user.getName()+"插入失败!"); } pstmt.close(); } catch (SQLException e) {//检测/捕获异常 System.out.println(e.getMessage()); } return i; }
在UserDaoSQL类的main方法中编写测试代码:
public static void main(String[] args) throws Exception{ // TODO Auto-generated method stub User u1 = new User("ma","123456"); UserDaoSQL userset =new UserDaoSQL(); userset.verify(u1.getName(),u1.getPwd()); User u2 = new User("xing5","123456"); userset.addUser(u2);//插入数据 userset.con.close();//关闭数据库 }
搜索用户方法:search()
public User search(String s){ try { Connection con = this.con;//建立数据库的连接 ResultSet rs = null; //建立一个空的结果集 //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; String sql = "select * from user where name=?"; PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql); //使用2. setXXX()系列方法为sql命令设置值 pstmt.setString(1, s);// //3.执行查询 rs = pstmt.executeQuery();//执行查询 int col = rs.getMetaData().getColumnCount();//获取字段数 //4. 打印数据表表头 System.out.println("============================"); for (int i = 1; i <= col; i++) { String col_name = rs.getMetaData().getColumnName(i); System.out.print(col_name+"\t"); } System.out.println(""); System.out.println("----------------------------"); //5. 打印查询到的记录 User u1 =null; while (rs.next()) { for (int i = 1; i <= col; i++) { System.out.print(rs.getString(i) + "\t"); } System.out.println(""); u1 = new User(rs.getString(2),rs.getString(3)); } System.out.println("============================"); pstmt.close(); return u1; }catch (SQLException e) {//捕捉、处理异常 System.out.println(e.getMessage()); } return null; }
在UserDaoSQL类的main方法中编写测试代码:
public static void main(String[] args) throws Exception{ // TODO Auto-generated method stub User u1 = new User("ma","123456"); UserDaoSQL userset =new UserDaoSQL(); userset.verify(u1.getName(),u1.getPwd()); User u2 = new User("xing6","123456"); userset.addUser(u2);//插入数据 User u3 = userset.search("xing6");//查询数据 System.out.println("名称为xing6的用户资料如下:"); System.out.println(u3.toString()); userset.con.close();//关闭数据库 }
列出全部记录:toString()
public String toString(){ try { Connection con = this.con;//建立数据库的连接 ResultSet rs = null; //建立一个空的结果集 String s="";//返回值 //1.创建PreparedStatement 对象,让它与一条SQL模板绑定; String sql = "select * from user"; PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql); //2.执行查询 rs = pstmt.executeQuery();//执行查询 int col = rs.getMetaData().getColumnCount();//获取字段数 //4. 打印数据表表头 System.out.println("============================"); for (int i = 1; i <= col; i++) { String col_name = rs.getMetaData().getColumnName(i);//获取字段名称 System.out.print(col_name+"\t"); } System.out.println(""); System.out.println("----------------------------"); //5. 打印查询到的记录 User u1 =null; while (rs.next()) { for (int i = 1; i <= col; i++) { System.out.print(rs.getString(i) + "\t"); } System.out.println(""); u1 = new User(rs.getString(2),rs.getString(3)); s += u1.toString()+"\r\n"; } System.out.println("============================"); pstmt.close(); return s; }catch (SQLException e) {//捕捉、处理异常 System.out.println(e.getMessage()); } return null; }
在UserDaoSQL类的main方法中编写测试代码:
public static void main(String[] args) throws Exception{ // TODO Auto-generated method stub User u1 = new User("ma","123456"); UserDaoSQL userset =new UserDaoSQL(); userset.verify(u1.getName(),u1.getPwd()); User u2 = new User("xing6","123456"); userset.addUser(u2);//插入数据 User u3 = userset.search("xing6");//查询数据 System.out.println("名称为xing6的用户资料如下:"); System.out.println(u3.toString()); System.out.println("全站所有用户如下:"); String s= userset.toString(); if(s!=null){ System.out.println(s); }else{ System.out.println("无符合条件的记录!"); } userset.con.close();//关闭数据库 }
实现接口
到现在为止,UserDaoSQL已经实现了UserDao接口的全部方法,
因此可以在类前做如下标识: