Insert byte[] into BIT data type in MySQL using Java

First, you need to create a database and table as below to work with the following sample.

	mysql> create database testdb;
	Query OK, 1 row affected (0.01 sec)
	mysql> CREATE TABLE testdb.bittypes(
	-> row_id INT,
	-> bit_val_1 BIT(1),
	-> bit_val_2 BIT(64),
	-> PRIMARY KEY (row_id)
	-> );
	Query OK, 0 rows affected (0.10 sec)
	mysql>
	mysql> describe testdb.bittypes;
	+-----------+---------+------+-----+---------+-------+
	| Field | Type | Null | Key | Default | Extra |
	+-----------+---------+------+-----+---------+-------+
	| row_id | int(11) | NO | PRI | NULL | |
	| bit_val_1 | bit(1) | YES | | NULL | |
	| bit_val_2 | bit(64) | YES | | NULL | |
	+-----------+---------+------+-----+---------+-------+
	3 rows in set (0.02 sec)
	mysql>

view raw createDatabaseTable.sql hosted with ❤ by GitHub

Sample:

	import java.io.ByteArrayInputStream;
	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.Statement;
	import java.util.Arrays;
	public class InsertByteArray {
	public static void main(String[] args) {
	String JdbcURL="jdbc:mysql://localhost:3306/testdb?useSSL=false";
	String Username="root";
	String password="root";
	try {
	Connection connection = DriverManager.getConnection(JdbcURL, Username, password);
	String sql = "INSERT INTO bittypes(id, bitVal1, bitVal2) VALUES (4, b'1', ?)";
	PreparedStatement pstmt = connection.prepareStatement(sql);
	byte[] buffer = new byte[] {1, 2, 5, 40};
	pstmt.setBinaryStream(1, new ByteArrayInputStream(buffer), buffer.length);
	System.out.println("pstmt");
	System.out.println(pstmt);
	pstmt.executeUpdate();
	pstmt.close();
	Statement stmt = connection.createStatement();
	ResultSet resultSet = stmt.executeQuery("SELECT * FROM bittypes");
	System.out.println("========================");
	while (resultSet.next()) {
	byte[] bytes = resultSet.getBytes("bitVal2");
	System.out.println(Arrays.toString(bytes));
	}
	} catch(Exception e) {
	e.printStackTrace();
	}
	}
	}

view raw InsertByteArray.java hosted with ❤ by GitHub