首页 > 其他专区 > Access >

java 如何实现连接access数据库并读取数据的操作

Access 2023-01-12

java 如何实现连接access数据库并读取数据的操作

 

具体步骤如下:

一、连接access数据库

创建AccessDbUtil类,连接数据库

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

import java.sql.Connection;

import java.sql.DriverManager;

 

/**

 * 获取Access连接

 *

 * @author dofun

 *

 */

public class AccessDbUtil

{

    public static Connection getDbConnection()

    {

        // 数据库url

        String url = "jdbc:Access:///E:ICD10.mdb";

        Connection conn = null;

        try

        {

            // 驱动加载

            Class.forName("com.hxtt.sql.access.AccessDriver").newInstance();

            conn = DriverManager.getConnection(url);

            return conn;

        }

        catch (Exception e)

        {

            System.out.println("Access连接失败");

        }

        return conn;

    }

}

二、读取Access数据并保存在mysql数据库

1、获取access数据库连接

2、查询表数据,并保存

3、关闭连接资源

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

/**

     * 同步疾病,手术

     *

     * @return

     */

    @RequestMapping(value = "importJbbm")

    @ResponseBody

    public String importJbbm()

    {

        // 获取数据库连接

        Connection conn = AccessDbUtil.getDbConnection();

        PreparedStatement pst = null;

        ResultSet rs = null;

        Boolean a = true;

        int id = 30000;

        try

        {

            for (int i = 1; i > 0; i++)

            {

                 

                // 手术

                pst = conn.prepareStatement("select * from sJBBMML where id > " + id + " and LB = 'S' ");

                List<IcdSsbm> jbs = new ArrayList<>();

                 

                rs = pst.executeQuery();

                while (a == rs.next())

                {

                    if (StringUtils.isNotBlank(rs.getString(2)))

                    {

                        IcdSsbm jb = new IcdSsbm();

                        jb.setCode(rs.getString(2));

                        jb.setName(rs.getString(5));

                        jb.setType(rs.getString(11));

                        jbs.add(jb);

                    }

                    else

                    {

                        a = false;

                        i = 0;

                    }

                    // System.out.println(rs.getString(2));

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

                    // System.out.println(rs.getString(11));

                }

                if (ListUtils.isNotEmpty(jbs))

                {

                    // 批量保存

                    icdSsbmService.saveBatch(jbs);

                }

                id += 1000;

            }

        }

        catch (SQLException e)

        {

        }

        finally

        {

            try

            {

                // 关闭资源

                rs.close();

                pst.close();

                conn.close();

            }

            catch (SQLException e)

            {

            }

        }

        return "导入完成";

    }

问题:

如果Access数据库设置了密码,获取连接的时候也加了密码参数,还是出现错误,找不到原因,最后就把Access数据库密码去掉了。

Access的分页问题,一次只能查询出1000条数据,其实Access本身是有分页查询的,只是看起很繁琐我并没有用,而且在数据量大的时候性能并不是很好。所以我是采用循环的形式,简单。

用的是Access_JDBC30.jar,java1.8竟然不支持Access了。网上好像有破解好的驱动,说是破了分页查询的限制。


Copyright © 2016-2023 office学习教程网 office.tqzw.net.cn. All Rights Reserved.