Java implements the operation of connecting to access database and reading data

The specific steps are as follows:

1. Connect to the access database

Create the AccessDbUtil class and connect to the database

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;
    }
}

2. Read Access data and save it in the mysql database

1. Obtain the access database connection

2. Query the table data and save it

3. Close the connection resource

/**
     * 同步疾病，手术
     *
     * @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 "导入完成";
    }

Question:

If the Access database has a password set, and the password parameter is added when obtaining the connection, an error still occurs, and the reason cannot be found, so the Access database password is finally removed.

The paging problem of Access is that only 1,000 pieces of data can be queried at a time. In fact, Access itself has paging query, but it seems very cumbersome and I have no use for it, and the performance is not very good when the amount of data is large. good. So I adopt the form of loop, which is simple.

Access_JDBC30.jar is used, but java1.8 does not support Access. There seems to be a cracked driver on the Internet, which is said to break the limitation of paging query.

Recommended tutorial: access database tutorial

The above is the detailed content of Java implements the operation of connecting to access database and reading data. For more information, please follow other related articles on the PHP Chinese website!