Java连接数据库MariaDB

一.安装配置open-jdk

  参考我之前写的博客:

    https://my.oschina.net/u/3887035/blog/1862936

 

二.安装配置MariaDB

  参考我另外一篇文章 MariaDB在Linux上安装配置与除错 https://my.oschina.net/u/3887035/blog/1862094

 

三.下载connector.jar

  直接去MariaDB官网下载connector:

    https://mariadb.com/downloads/mariadb-tx/connector

  下载你需要的jar包.

  当然,你也可以用wget -i -c url(jar包的url)来获取该jar包.

 

四.测试

  1.将jar包放在正确的位置.

    放在${JRE_HOME}/lib/ext (也就是/usr/lib/jvm/java-xxxxxxx/jre/lib/ext)中,针对整个系统有效.

    如果是web后端的代码,使用tomcat做web server,放在tomcat的目录下的lib里也是可以的.

  2.测试用代码

    (1)首先你得有用户和密码,(建议不用root,当然用root也可以)

    (2)创建数据库test:

      create database test;

      use test;

 

    (3)建立数据库表(对菜鸟教材上的MySQL的SQL语句做了一些修改,可能不适用,你可以自己做一些修改):

    CREATE TABLE `website` (

      `id` int(11) NOT NULL AUTO_INCREMENT,

      `name` char(20) NOT NULL DEFAULT '' COMMENT '站点名称',

      `url` varchar(255) NOT NULL DEFAULT '',

      `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',

      `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',

       PRIMARY KEY (`id`)

    ) DEFAULT CHARSET=utf8;

 

    (4)插入数据(对菜鸟教材上的MySQL的SQL语句做了一些修改,可能不适用,你可以自己做一些修改):

      INSERT INTO `websites` VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA'), ('2', '淘宝', 'https://www.taobao.com/', '13', 'CN'), ('3', '菜鸟教程', 'http://www.runoob.com', '5892', ''), ('4', '微博', 'http://weibo.com/', '20', 'CN'), ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');

 

 

    (5)测试用java代码:  

import java.sql.*;

public class MariaDBDemo{
    static final String MARIADBDIVER="org.mariadb.jdbc.Driver"; // This is the fixed format to connect the Dirver Manager.
    static final String MARIADBURL="jdbc:mariadb://localhost:3306/test"; // This is the url pattern.
    static final String USER="your_user";
    static final String PWD="your_password";

    public static void main(String[] args){
        Connection connection=null;
        Statement statement=null;
        try{
            /* step1 Import the Driver Manager*/
            Class.forName(MARIADBDIVER);
            System.out.println("Connecting the database!");
            /* step2 Create the connection with DataBase */
            connection=DriverManager.getConnection(MARIADBURL,USER,PWD);

            System.out.println("Instant the Statement ...");
            /* step3 create an instance of the connection */
            statement=connection.createStatement();

            /* step4 code SQL words */
            String sql;
            sql="SELECT cID,cName FROM CUSTOMERS";

            /* step5 Use the instance "statement" to execute the SQL and assign the value to the instance "rs" of object "ResultSet" */
            ResultSet rs=statement.executeQuery(sql);

            while(rs.next()){

                int id = rs.getInt("id");

                String name = rs.getString("name");

                String url = rs.getString("url");

 

                System.out.println("ID: " + id);

                System.out.println("siteName: " + name);

                System.out.println("site_URL: " + url);               
            }
            /* close the instance "rs" "statement" "connection" in order */
            rs.close();
            statement.close();
            connection.close();
        }
        /* catch the exception */
        catch(SQLException e){
            e.printStackTrace();
        }
        catch(Exception e){
            e.printStackTrace();
        }
        finally{
            /* Asure close the instance "statement" and "connection" */
            try{
                if(statement!=null) statement.close();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
            try{
                if(connection!=null) connection.close();
            }
            catch(SQLException e){
                e.printStackTrace();
            }
        }
    }

    

    记得更换代码中的用户名和密码,然后编译完成,进行测试.

 

五.改善

  进行数据库操作的时候使用 PreparedStatement 更好，好处如下：

        1.PreparedStatement可以写动态参数化的查询；

        2.PreparedStatement比 Statement 更快；

        3.PreparedStatement可以防止SQL注入式攻击

    例如:

        PreparedStatement ps=null;

        sql="SELECT id,name,url FROM websites";

        ps=connection.prepareStatement(sql);

        ResultSet rs=ps.executeQuery();

        ...

 

转载于:https://my.oschina.net/u/3887035/blog/1863200