Skip to main content

How to Work With Databend in Java

Before You Begin

Create Databend User

mysql -h127.0.0.1 -uroot -P3307

Create a User

CREATE USER user1 IDENTIFIED BY 'abc123';

Grants Privileges

Grants ALL privileges to the user user1:

GRANT ALL on *.* TO user1;

Java

This guideline show how to connect and query to Databend using JDBC. We will be creating a table named books and insert a row, then query it.

demo.java

demo.java
import java.sql.*;

public class demo {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://127.0.0.1:3307/default";

static final String USER = "user1";
static final String PASS = "abc123";

public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try{
Class.forName(JDBC_DRIVER);
conn = DriverManager.getConnection(DB_URL,USER,PASS);

stmt = conn.createStatement();
String create_sql = "CREATE DATABASE IF NOT EXISTS book_db";
int rs1 = stmt.executeUpdate(create_sql);

String use_sql = "USE book_db";
int rs2 = stmt.executeUpdate(use_sql);

String ct_sql = "CREATE TABLE IF NOT EXISTS books(title VARCHAR, author VARCHAR, date VARCHAR)";
int rs3 = stmt.executeUpdate(ct_sql);


// Insert new book.
String title = "mybook";
String author = "author";
String date = "2022";
String add_book = "INSERT INTO books (title, author, date) VALUES ('"+ title +"', '"+ author +"', '" + date + "')";
int rs4 = stmt.executeUpdate(add_book);


// Select book
String sql = "SELECT * FROM books";
ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {
String col1 = rs.getString("title");
String col2 = rs.getString("author");
String col3 = rs.getString("date");

System.out.print("title: " + col1 + ", author: " + col2 + ", date: " + col3);
}
// Close conn
rs.close();
stmt.close();
conn.close();
} catch(SQLException se) {
// throw JDBC err
se.printStackTrace();
} catch(Exception e) {
// throw Class.forName err
e.printStackTrace();
} finally {
// Close source
try{
if(stmt!=null) stmt.close();
} catch(SQLException se2) {
}
try{
if (conn!=null) conn.close();
} catch(SQLException se) {
se.printStackTrace();
}
}
}
}

Run demo.java

In this case:

The demo classpath is in the folder: /home/eason/database/source-code/test/out/test/test

The jdbc class is in the folder: /home/eason/Downloads/jar_files/mysql-connector-java-5.1.48.jar

$ ~/.jdks/openjdk-17.0.1/bin/java -Dfile.encoding=UTF-8 -classpath /home/eason/database/source-code/test/out/test/test:/home/eason/Downloads/jar_files/mysql-connector-java-5.1.48.jar demo
title: mybook, author: author, date: 2022
Outputs
title: mybook, author: author, date: 2022