登录
目录
当前位置： 首页 > 文档资料 > hi-nginx-java 帮助文档 >

数据库

优质
小牛编辑
132浏览
2023-12-01

web 开发离不开数据库。最常见的的数据库是mariadb或者mysql。

为了使用数据库，需要下载访问数据库的库。对mariadb而言，即MariaDB Connector/J。

下载该库，将其安装至/usr/local/nginx/java中。修改/etc/profile.d/jdk.sh中的CLASSPATH,添加该库:


export CLASSPATH=/usr/local/nginx/java:/usr/local/nginx/java/hi-nginx-java.jar:/usr/local/nginx/java/mariadb-java-client-2.7.1.jar

运行source /etc/profile更新环境CLASSPATH。

修改/usr/local/nginx/conf/nginx.conf:

hi_java_classpath "-Djava.class.path=.:/usr/local/nginx/java:/usr/local/nginx/java/hi-nginx-java.jar:/usr/local/nginx/java/mariadb-java-client-2.7.1.jar:/usr/local/nginx/java/app.jar"

假设有一个testdb，其中有一张表:

CREATE TABLE `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',
  `url` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

且向该表插入些数据:

INSERT INTO `websites`(`name`,`url`) VALUES ('Google', 'https://www.google.com/'), ('淘宝', 'https://www.taobao.com/');

那么，给定application.conf中的数据库配置如下:

mariadb {
    driver = "org.mariadb.jdbc.Driver"
    url = "jdbc:mariadb://localhost:3306/testdb"
    username = root
    password = 123456
}

为了获得查询该表的一个服务http://localhost/test/db.java，可以编写类test.db:

package test;

import hi.request;
import hi.response;
import hi.route;
import java.util.regex.Matcher;
import java.util.ArrayList;
import java.util.HashMap;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;

import org.mariadb.jdbc.MariaDbPoolDataSource;

public class db implements hi.route.run_t {

    private static class db_help {
        private static MariaDbPoolDataSource ds = null;

        private static db_help instance = new db_help();

        private db_help() {

        }

        public static db_help get_instance() {
            return db_help.instance;
        }

        public MariaDbPoolDataSource get_data_source() throws SQLException {
            if (db_help.ds != null) {
                return db_help.ds;
            }
            db_help.ds = new MariaDbPoolDataSource(hi.route.get_instance().get_config().getString("mariadb.url"));
            db_help.ds.setUser(hi.route.get_instance().get_config().getString("mariadb.username"));
            db_help.ds.setPassword(hi.route.get_instance().get_config().getString("mariadb.password"));
            return db_help.ds;
        }
    }

    public db() {

    }

    public void handler(hi.request req, hi.response res, Matcher m) {
        String sql = "SELECT * FROM `websites` ORDER BY `id` LIMIT 0,5;";
        ResultSetHandler<ArrayList<HashMap<String, Object>>> h = (ResultSet rs) -> {
            ArrayList<HashMap<String, Object>> result = new ArrayList<HashMap<String, Object>>();
            while (rs.next()) {
                HashMap<String, Object> row = new HashMap<String, Object>();
                ResultSetMetaData meta = rs.getMetaData();
                int cols = meta.getColumnCount();

                for (int i = 0; i < cols; i++) {
                    row.put(meta.getColumnName(i + 1), rs.getObject(i + 1));
                }
                result.add(row);
            }
            return result;
        };
        try {
            QueryRunner qr = new QueryRunner(db_help.get_instance().get_data_source());
            ArrayList<HashMap<String, Object>> result = qr.query(sql, h);
            StringBuffer content = new StringBuffer();
            for (HashMap<String, Object> item : result) {
                for (HashMap.Entry<String, Object> iter : item.entrySet()) {
                    content.append(String.format("%s = %s\n", iter.getKey(), iter.getValue().toString()));
                }
                content.append("\n\n");
            }

            res.content = content.toString();
            res.status = 200;
        } catch (SQLException e) {
            res.content = e.getMessage();
            res.status = 500;
        }
    }
}

访问http://localhost/test/db.java可以获得相应数据。

利用org.apache.commons.dbutils.handlers.MapListHandler类，以上handler可进一步简化：


import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.handlers.MapListHandler;

    public void handler(hi.request req, hi.response res, Matcher m) {
        String sql = "SELECT * FROM `websites` ORDER BY `id` LIMIT 0,5;";
        try {
            QueryRunner qr = new QueryRunner(db_help.get_instance().get_data_source());
            List<Map<String, Object>> result = qr.query(sql, new MapListHandler());
            StringBuffer content = new StringBuffer();
            for (Map<String, Object> item : result) {
                for (Map.Entry<String, Object> iter : item.entrySet()) {
                    content.append(String.format("%s = %s\n", iter.getKey(), iter.getValue().toString()));
                }
                content.append("\n\n");
            }

            res.content = content.toString();
            res.status = 200;
        } catch (SQLException e) {
            res.content = e.getMessage();
            res.status = 500;
        }
    }

MySQL

对mysql而言,即，MySQL Connector/J。

修改CLASSPATH:

export CLASSPATH=/usr/local/nginx/java:/usr/local/nginx/java/hi-nginx-java.jar:/usr/local/nginx/java/mysql-connector-java-8.0.22.jar

修改hi-nginx配置:

hi_java_classpath "-Djava.class.path=.:/usr/local/nginx/java:/usr/local/nginx/java/hi-nginx-java.jar:/usr/local/nginx/java/mysql-connector-java-8.0.22.jar:/usr/local/nginx/java/app.jar"

添加mysql配置至application.conf中:

mysql {
    driver = "org.mysql.cj.jdbc.Driver"
    url = "jdbc:mysql://localhost:3306/testdb"
    username = root
    password = 123456
}

修改db_help类:

import com.mysql.cj.jdbc.MysqlConnectionPoolDataSource;

    private static class db_help {
        private static MysqlConnectionPoolDataSource ds = null;

        private static db_help instance = new db_help();

        private db_help() {

        }

        public static db_help get_instance() {
            return db_help.instance;
        }

        public MysqlConnectionPoolDataSource get_data_source() throws SQLException {
            if (db_help.ds != null) {
                return db_help.ds;
            }
            db_help.ds = new MysqlConnectionPoolDataSource();
            db_help.ds.setURL(hi.route.get_instance().get_config().getString("mysql.url"));
            db_help.ds.setUser(hi.route.get_instance().get_config().getString("mysql.username"));
            db_help.ds.setPassword(hi.route.get_instance().get_config().getString("mysql.password"));
            return db_help.ds;
        }
    }

Druid 连接池

添加druid库及其hi-nginx配置:

hi_java_classpath "-Djava.class.path=.:/usr/local/nginx/java:/usr/local/nginx/java/hi-nginx-java.jar:/usr/local/nginx/java/mysql-connector-java-8.0.22.jar:/usr/local/nginx/java/druid-1.2.3.jar:/usr/local/nginx/java/app.jar"

之后，修改db_help如下:

import com.alibaba.druid.pool.DruidDataSource;


    private static class db_help {
        private static DruidDataSource ds = null;

        private static db_help instance = new db_help();

        private db_help() {

        }

        public static db_help get_instance() {
            return db_help.instance;
        }

        public DruidDataSource get_data_source() throws SQLException {
            if (db_help.ds != null) {
                return db_help.ds;
            }
            db_help.ds = new DruidDataSource();
            db_help.ds.setUrl(hi.route.get_instance().get_config().getString("mysql.url"));
            db_help.ds.setUsername(hi.route.get_instance().get_config().getString("mysql.username"));
            db_help.ds.setPassword(hi.route.get_instance().get_config().getString("mysql.password"));
            return db_help.ds;
        }
    }

Bean

要使用bean，要导入BeanHandler,BeanListHandler或者BeanMapHandler。

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;

定义classwebsite:

    public class website {
        private int id;
        private String url;
        private String name;

        public void setId(int id) {
            this.id = id;
        }

        public int getId() {
            return this.id;
        }

        public void setUrl(String url) {
            this.url = url;
        }

        public String getUrl() {
            return this.url;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getName() {
            return this.name;
        }
    }

然后重写handler：

    public void handler(hi.request req, hi.response res, Matcher m) {
        String sql = "SELECT * FROM `websites` ORDER BY `id` LIMIT 0,5;";
        try {
            QueryRunner qr = new QueryRunner(db_help.get_instance().get_data_source());
            List<website> result = qr.query(sql, new BeanListHandler<website>(website.class));
            StringBuffer content = new StringBuffer();

            for (website item : result) {
                content.append(
                        String.format("id = %s\tname = %s\turl = %s\n", item.getId(), item.getName(), item.getUrl()));
            }

            res.content = content.toString();
            res.status = 200;
        } catch (SQLException e) {
            res.content = e.getMessage();
            res.status = 500;
        }
    }

若使用BeanMapHandler,则修改如下:


    public void handler(hi.request req, hi.response res, Matcher m) {
        String sql = "SELECT * FROM `websites` ORDER BY `id` LIMIT 0,5;";
        try {
            QueryRunner qr = new QueryRunner(db_help.get_instance().get_data_source());
            Map<String, website> result = qr.query(sql, new BeanMapHandler<String, website>(website.class));
            StringBuffer content = new StringBuffer();

            for (Map.Entry<String, website> item : result.entrySet()) {
                content.append(String.format("%s\tid = %s\tname = %s\turl = %s\n", item.getKey(),
                        item.getValue().getId(), item.getValue().getName(), item.getValue().getUrl()));
            }

            res.content = content.toString();
            res.status = 200;
        } catch (SQLException e) {
            res.content = e.getMessage();
            res.status = 500;
        }
    }
免责声明： 以上内容版权归原作者所有，如有侵犯您的原创版权请告知，我们将尽快删除相关内容。感谢每一位辛勤著写的作者，感谢每一位的分享。
@小牛知识库 xnip.cn