一、插入圖檔
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestAdd {
public void testAdd() {
// 1.create sql ;
String sql = "insert into userinfo ( username , password , image) values (?,?,?)";
// 2.get connection
Connection conn = null;
PreparedStatement psmt = null;
InputStream is = null;
try {
conn = JdbcHelper.getConnection();
// 3.prepare sql
psmt = conn.prepareStatement(sql);
// 4.set params
psmt.setString(1, "javaee");
psmt.setString(2, "123456");
// (1)get the stream
is = new FileInputStream("F:/androidgo/院内資訊資源整合系統建立.bmp");
psmt.setBinaryStream(3, is, is.available());
// 5.update db
psmt.executeUpdate();
System.out.println("ok!");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 6.close db
try {
if (psmt != null)
psmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
TestAdd test = new TestAdd();
test.testAdd();
}
}
二、擷取圖檔
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestAdd {
public void testAdd() {
// 1.create sql ;
String sql = "insert into userinfo ( username , password , image) values (?,?,?)";
// 2.get connection
Connection conn = null;
PreparedStatement psmt = null;
InputStream is = null;
try {
conn = JdbcHelper.getConnection();
// 3.prepare sql
psmt = conn.prepareStatement(sql);
// 4.set params
psmt.setString(1, "javaee");
psmt.setString(2, "123456");
// (1)get the stream
is = new FileInputStream("F:/androidgo/院内資訊資源整合系統建立.bmp");
psmt.setBinaryStream(3, is, is.available());
// 5.update db
psmt.executeUpdate();
System.out.println("ok!");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 6.close db
try {
if (psmt != null)
psmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
TestAdd test = new TestAdd();
test.testAdd();
}
} 三、助手類
import java.sql.Connection;
import java.sql.DriverManager;
public class JdbcHelper {
private static Connection conn;
private JdbcHelper() {
}
// get the connection to db
public static synchronized Connection getConnection() throws Exception {
if (conn == null)
initConnection();
else if (conn.isClosed())
initConnection();
return conn;
}
//
private static void initConnection() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://192.168.1.101:3306/android",
"root", "123456");
}
}
四、資料庫檔案
CREATE TABLE `userinfo` (
`id` int(10) unsigned NOT NULL auto_increment,
`username` varchar(45) default NULL,
`password` varchar(45) default NULL,
`image` longblob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;