jdbc连接mysql数据库封装_JDBC连接数据库方法的封装，以及查询数据方法的封装（精华）...

主要内容：

一般的连接数据库测试

把连接数据库的方法封装成一个类和测试

一个简单的插入表实例

查询数据实例

封装查询的数据库的信息

封装信息后的查询数据库

一.一般的数据库连接测试

1 public class TestConnection1 {

2 public static void main(String[] args) throws Exception {

3 Class.forName("com.mysql.jdbc.Driver");

4 String url="jdbc:mysql://localhost:3306/test?"//数据库url

5 + "useUnicode=true&characterEncoding=UTF8";//防止乱码

6 String user="h4";

7 String pass="111";

8 Connection conn=DriverManager.getConnection(url, user, pass);

9

10 System.out.println(conn+",成功连接数据库");

11 conn.close();

12 }

13 }

二.我们不可能每写一个处理信息功能就写一次连接，这样太麻烦，那么为了方便以后的应用，我们通常把数据库连接封装起来。

具体实现步骤如下：

1.定义变量：

private static String DRIVER_CLASS;

private static String URL;

private static String USERRNAME;

private static String PASSWORD;

2.在你建的eclipse根目录下新建一个File文件Properties；

文件内容为你定义的变量所指向的对象：

driver=com.mysql.jdbc.Driver

url=jdbc:mysql://localhost:3306/test? useUnicode=true&characterEncoding=UTF8

user=h4

pass=111

3.构建一个Properties对象：Properties p=new Properties();

4. java.io下的类FileInputStream的方法；FileInputStream(String name) ：通过打开一个到实际文件的连接来创建一个 FileInputStream，该文件通过文件系统中的路径名 name 指定。

来获取这个文件里面的资料：FileInputStream fis=new FileInputStream("db.properties");

5. 用3构建的变量p来下载资料：p.load(fis);

6.利用getProperty();获取参数：

DRIVER_CLASS=p.getProperty("driver");

URL=p.getProperty("url");

USERRNAME=p.getProperty("user");

PASSWORD=p.getProperty("pass");

7.写一个连接数据库的方法getConection()；

8.写一个关闭数据库的方法close(Connection conn)；

写好后代码如下：

1 public class jdbcutil {

2 private static String DRIVER_CLASS;

3 private static String URL;

4 private static String USERRNAME;

5 private static String PASSWORD;

6 private static Properties p=new Properties();

7 static{

8 try {

9 FileInputStream fis=new FileInputStream("db.properties");

10 p.load(fis);

11 DRIVER_CLASS=p.getProperty("driver");

12 URL=p.getProperty("url");

13 USERRNAME=p.getProperty("user");

14 PASSWORD=p.getProperty("pass");

15 Class.forName(DRIVER_CLASS);

16 fis.close();

17 } catch (IOException e) {

18 e.printStackTrace();

19 } catch (ClassNotFoundException e) {

20 e.printStackTrace();

21 }

22 }

23 public static Connection getConection(){

24 Connection conn=null;

25 try{

26 conn=DriverManager.getConnection(URL, USERRNAME, PASSWORD);

27 }

28 catch (Exception e) {

29 e.printStackTrace();

30 }

31 return conn;

32 }

33 public static void close(Connection conn) {

34 try {

35 if (conn != null)

36 conn.close();

37 } catch (Exception e) {

38 e.printStackTrace();

39 }

40 }

41

42 }

那么封装好之后，我们来写一个测试类，测试连接

1 public class TestConnection2 {

2

3 public static void main(String[] args) throws Exception {

4 Connection conn=jdbcutil.getConection();//利用封装好的类名来调用连接方法便可

5 System.out.println(conn+",成功连接数据库");

6 jdbcutil.close( conn);//同样利用类名调用关闭方法即可

7 }

8 }

三.连接成功，我们写一个简单的向数据库插入表的实例。

1 public class TestDDl {

2

3 public static void main(String[] args) {

4 Connection conn=null;

5 Statement stmt=null;

6 conn=jdbcutil.getConection();//连接数据库

7 String createTableSql= " create table user_test1( "+//记住引号和单词间一定要有空格

8 " id int, "+

9 " name varchar(32) , "+

10 " password varchar(32) , "+

11 " birthday date "+

12 " ) ";

13 try {

14 stmt=conn.createStatement();

15 stmt.execute(createTableSql);

16 } catch (SQLException e) {

17 e.printStackTrace();

18 }

19 jdbcutil.close(null, stmt, conn);//关闭数据库

20 }

21 }

四.我们在写一个查询数据库数据的实例。(有三种方法)

1 public class TestDQL {

2 public static void main(String[] args){

3 Connection conn=null;//定义为空值

4 Statement stmt=null;

5 ResultSet rs=null;

6 String sql="select * from employees";//sql语句

7 conn=jdbcutil.getConection();

8 try {

9 stmt=conn.createStatement();//创建一个Statement语句对象

10 rs=stmt.executeQuery(sql);//执行sql语句

11 while(rs.next()){

12 System.out.print(rs.getInt(1)+",");

13 System.out.print(rs.getString(2)+",");//直接使用参数

14 System.out.print(rs.getString(3)+",");

15 System.out.print(rs.getString(4)+",");

16 System.out.println(rs.getString(5));

17 }

18 } catch (SQLException e) {

19 e.printStackTrace();

20 }finally{

21 jdbcutil.close(rs,stmt,conn);//关闭数据库

22 }

23 }

24 }

//第二种方法如下：

1 public class TestDQl2 {

2

3 public static void main(String[] args) {

4 Connection conn=null;

5 Statement stmt=null;

6 ResultSet rs=null;

7 String sql="select * from employees";

8 conn=jdbcutil.getConection();

9 try {

10 stmt=conn.createStatement();

11 rs=stmt.executeQuery(sql);

12 while(rs.next()){

13 System.out.print(rs.getInt("userid")+",");//里面直接写要查找的内容名称

14 System.out.print(rs.getString("employee_id")+",");

15 System.out.print(rs.getString("last_name")+",");

16 System.out.print(rs.getString("salary")+",");

17 System.out.println(rs.getString("department_id"));

18 }

19 } catch (SQLException e) {

20 e.printStackTrace();

21 }finally{

22 jdbcutil.close(rs,stmt,conn);

23 }

24 }

25 }

1 //第三种方法如下：

2 public class TestDQL3 {

3 public static void main(String[] args) {

4 Connection conn=null;

5 Statement stmt=null;

6 ResultSet rs=null;

7 String sql="select * from employees";

8 conn=jdbcutil.getConection();

9 try {

10 stmt=conn.createStatement();

11 rs=stmt.executeQuery(sql);

12 while(rs.next()){

13 int index=1;

14 System.out.print(rs.getInt(index++)+",");

15 System.out.print(rs.getString(index++)+",");

16 System.out.print(rs.getString(index++)+",");

17 System.out.print(rs.getString(index++)+",");

18 System.out.println(rs.getString(index++));

19 }

20 } catch (SQLException e) {

21 e.printStackTrace();

22 }finally{

23 jdbcutil.close(rs,stmt,conn);

24 }

25 }

26 }

五.在四里面我们写了查询员工资料的信息，但是有的时候我们要保存起来方便之后更好的查找，那怎么办呢？没错，封装。

1 public class employees implements Serializable {

2 private Integer userid;

3 private String employee_id;

4 private String last_name;

5 private String salary;

6 private String department_id;

7

8 public employees() {

9 super();

10 }

11

12 public employees(String employee_id, String last_name, String salary, String department_id) {

13 super();

14 this.employee_id = employee_id;

15 this.last_name = last_name;

16 this.salary = salary;

17 this.department_id = department_id;

18 }

19

20 @Override

21 public String toString() {

22 return "employees [userid=" + userid + ", employee_id=" + employee_id + ", last_name=" + last_name

23 + ", salary=" + salary + ", department_id=" + department_id + "]";

24 }

25

26 public Integer getUserid() {

27 return userid;

28 }

29

30 public void setUserid(Integer userid) {

31 this.userid = userid;

32 }

33

34 public String getEmployee_id() {

35 return employee_id;

36 }

37

38 public void setEmployee_id(String employee_id) {

39 this.employee_id = employee_id;

40 }

41

42 public String getLast_name() {

43 return last_name;

44 }

45

46 public void setLast_name(String last_name) {

47 this.last_name = last_name;

48 }

49

50 public String getSalary() {

51 return salary;

52 }

53

54 public void setSalary(String salary) {

55 this.salary = salary;

56 }

57

58 public String getDepartment_id() {

59 return department_id;

60 }

61

62 public void setDepartment_id(String department_id) {

63 this.department_id = department_id;

64 }

65 }

六.封装好后的查询和上面没封装之前有点变化。

1 public class TestDQL4 {

2 public static void main(String[] args) {

3 Connection conn=null;

4 Statement stmt=null;

5 ResultSet rs=null;

6 List emps=new ArrayList<>();//构造集合对象

7

8 String sql="select * from employees";

9

10 conn=jdbcutil.getConection();//获取数据库连接

11

12 try {

13 stmt=conn.createStatement();

14 rs=stmt.executeQuery(sql);

15 while(rs.next()){//遍历结果集

16 int index=1;

17 employees emp=new employees();//构造员工类对象

18 emp.setUserid(rs.getInt(index++));//获取值

19 emp.setEmployee_id(rs.getString(index++));

20 emp.setLast_name(rs.getString(index++));

21 emp.setSalary(rs.getString(index++));

22 emp.setDepartment_id(rs.getString(index++));

23 emps.add(emp);//放到集合中去

24 }

25 } catch (SQLException e) {

26 e.printStackTrace();

27 }finally{

28 jdbcutil.close(rs,stmt,conn);//关闭连接

29 }

30 for(employees emp:emps){//遍历

31 System.out.println(emp);

32 }

33 }

34 }

其实我们可以继续封装，把遍历结果集给封装起来。

1 public class TestDQL5 {

2

3 public static void main(String[] args) {

4 Connection conn=null;

5 Statement stmt=null;

6 ResultSet rs=null;

7 List emps=new ArrayList<>();

8

9 String sql="select * from employees";

10

11 conn=jdbcutil.getConection();

12

13 try {

14 stmt=conn.createStatement();

15 rs=stmt.executeQuery(sql);

16 emps=resultSetToEmployees(rs);

17 } catch (SQLException e) {

18 e.printStackTrace();

19 }finally{

20 jdbcutil.close(rs,stmt,conn);

21 }

22 for(employees emp:emps){

23 System.out.println(emp);

24 }

25 }

26 public static List resultSetToEmployees(ResultSet rs){

27 List emps=new ArrayList<>();

28 try {

29 while(rs.next()){

30 int index=1;

31 employees emp=new employees();

32 emp.setUserid(rs.getInt(index++));

33 emp.setEmployee_id(rs.getString(index++));

34 emp.setLast_name(rs.getString(index++));

35 emp.setSalary(rs.getString(index++));

36 emp.setDepartment_id(rs.getString(index++));

37 emps.add(emp);

38 }

39 } catch (SQLException e) {

40 e.printStackTrace();

41 }

42

43 return emps;

44 }

45 }

如果是一个人查询信息呢？还可以这样封装。

1 public class TestDQL6 {

2 public static void main(String[] args) {

3 Connection conn=null;

4 Statement stmt=null;

5 ResultSet rs=null;

6 List emps=new ArrayList<>();

7

8 String sql="select * from employees";

9

10 conn=jdbcutil.getConection();

11

12 try {

13 stmt=conn.createStatement();

14 rs=stmt.executeQuery(sql);

15 while(rs.next()){

16 employees emp=resultSetToEmployee(rs);

17 emps.add(emp);

18 }

19 } catch (SQLException e) {

20 e.printStackTrace();

21 }finally{

22 jdbcutil.close(rs,stmt,conn);

23 }

24 for(employees emp:emps){

25 System.out.println(emp);

26 }

27 }

28 public static employees resultSetToEmployee(ResultSet rs){

29 employees emp=null;

30 try {

31 int index=1;

32 emp=new employees();

33 emp.setUserid(rs.getInt(index++));

34 emp.setEmployee_id(rs.getString(index++));

35 emp.setLast_name(rs.getString(index++));

36 emp.setSalary(rs.getString(index++));

37 emp.setDepartment_id(rs.getString(index++));

38 } catch (SQLException e) {

39 e.printStackTrace();

40 }

41 return emp;

42 }

43