java mysql 加密_mysql加密解密函数与java操作

mysql下的加密函数有如下几个

PASSWORD()：创建一个经过加密的密码字符串，适合于插入到MySQL的安全系

统。该加密过程不可逆，和unix密码加密过程使用不同的算法。主要用于MySQL的认证系统。

ENCRYPT(，)：使用UNIX crypt()系统加密字符串，ENCRYPT()函数接收要加密的字符串和(可选的)用于加密过程的salt(一个可以唯一确定口令的字符串，就像钥匙一样)，注意，windows上不支持

ENCODE(,)   DECODE(,)：加密解密字符串。该函数有两个参数：被加密或解密的字符串和作为加密或解密基础的密钥。Encode结果是一个二进制字符串，以BLOB类型存储。加密成都相对比较弱

MD5()：计算字符串的MD5校验和(128位)

SHA5()：计算字符串的SHA5校验和(160位)

以上两个函数返回的校验和是16进制的，适合与认证系统中使用的口令。

AES_ENCRYPT AES_DECRYPT示例

insert into users(test) values(AES_ENCRYPT('teststr','salt'))；

select AES_DECRYPT(test,'salt') from users；

AES_ENCRYPT AES_DECRYPT实验：(注意数据库中的sid是自增的，不必去报与本实验一致)

经加密后的数据：

通过key取回加密后的数据：

Java编程具体操作，插入和查询(以以上user表为例)

Java数据库操作类，主要封装了java对数据库的基本操作

比如如果需要插入数据只需要设定sql语句和paramers参数的值即可

String sql = "insert into user(sname,password) values(?,AES_ENCRYPT(?,?))";

String paramers[] = { user.getSname(), user.getPassword(),

user.getPassword() };

package com.chen.toolsbean;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

public class SqlHelper {

private static Connection conn;

private static PreparedStatement pStream = null;

private static ResultSet rSet = null;

private static String username;

private static String password;

private static String driver;

private static String url;

// 使用静态块加载驱动

static {

try {

Properties properties = new Properties();

InputStream is = SqlHelper.class

.getClassLoader()

.getResourceAsStream("com/chen/toolsbean/dbinfo.properties");

properties.load(is);

url = (String) properties.getProperty("url");

username = (String) properties.getProperty("username");

password = (String) properties.getProperty("password");

driver = (String) properties.getProperty("driver");

Class.forName(driver);

} catch (Exception e) {

e.printStackTrace();

}

}

public static Connection openConnection() {

try {

return DriverManager.getConnection(url, username, password);

} catch (Exception e) {

e.printStackTrace();

}

return null;

}

// 同一的cud操作

public static void executeUpdate(String sql, String[] parameters) {

try {

conn = openConnection();

pStream = conn.prepareStatement(sql);

if (parameters != null) {

for (int i = 0; i < parameters.length; i++) {

pStream.setString(i + 1, parameters[i]);

}

}

pStream.executeUpdate();

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e.getMessage());

} finally {

close(null, pStream, conn);

}

}

// 同一的cud操作

public static void executeUpdate2(String sql[], String[][] parameters) {

try {

conn = openConnection();

// conn设为不要自动提交

conn.setAutoCommit(false);

for (int i = 0; i < sql.length; i++) {

pStream = conn.prepareStatement(sql[i]);

if (parameters[i] != null) {

for (int j = 0; j < parameters[i].length; j++) {

pStream.setString(j + 1, parameters[i][j]);

}

pStream.executeUpdate();

}

}

conn.commit();

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e.getMessage());

} finally {

close(null, pStream, conn);

}

}

public static Connection getConn() {

return conn;

}

public static PreparedStatement getpStream() {

return pStream;

}

public static ResultSet getrSet() {

return rSet;

}

// 写一个方法，完成查询任务

// sql表示要执行的sql语句

// select * from emp where ename=?

public static ResultSet executeQuery(String sql, String[] parameters) {

// 根据实际情况，对sql语句的？赋值

try {

conn = DriverManager.getConnection(url, username, password);

// 创建pStream对象<==>sql语句

pStream = conn.prepareStatement(sql);

// 如果parameters不为null,才去赋值

if (parameters != null) {

for (int i = 0; i < parameters.length; i++) {

pStream.setString(i + 1, parameters[i]);

}

}

rSet = pStream.executeQuery();

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e.getMessage());

} finally {

}

return rSet;

}

public static void close(ResultSet rSet, Statement pStream, Connection conn) {

if (rSet != null) {

try {

rSet.close();

} catch (SQLException e) {

e.printStackTrace();

}

rSet = null;

}

if (pStream != null) {

try {

pStream.close();

} catch (SQLException e) {

e.printStackTrace();

}

pStream = null;

}

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

conn = null;

}

}

}

User.java user表的domain对象

package com.domain;

public class User {

private String sid;

private String sname;

private String password;

public String getSid() {

return sid;

}

public void setSid(String sid) {

this.sid = sid;

}

public String getSname() {

return sname;

}

public void setSname(String sname) {

this.sname = sname;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

}

Userdao.java往数据库中添加数据，以及插叙数据

package com.domain.dao;

import java.sql.ResultSet;

import com.chen.toolsbean.SqlHelper;

import com.domain.User;

public class UserDao {

public static void insert(User user) {

String sql = "insert into user(sname,password) values(?,AES_ENCRYPT(?,?))";

String paramers[] = { user.getSname(), user.getPassword(),

user.getPassword() };

SqlHelper.executeUpdate(sql, paramers);

}

public static boolean check(User user) {

String sql = "select sid,sname from user where AES_DECRYPT(password,?)=?";

String paramers[] = { user.getPassword(), user.getPassword() };

ResultSet rs = null;

boolean flag = false;

try {

rs = SqlHelper.executeQuery(sql, paramers);

if (rs.next()) {

flag = true;

}

} catch (Exception e) {

flag = false;

e.printStackTrace();

} finally {

SqlHelper.close(rs, null, null);

}

return flag;

}

}

Junit测试：

import org.junit.Before;

import org.junit.BeforeClass;

import org.junit.Test;

import com.domain.User;

import com.domain.dao.UserDao;

public class TestUserDao {

@BeforeClass

public static void setUpBeforeClass() throws Exception {

}

@Before

public void setUp() throws Exception {

}

// 插入一个user(sname,password) values(java,java)

@Test

public void testInsert() {

User user = new User();

user.setSname("java");

user.setPassword("java");

UserDao.insert(user);

}

// 查询刚才插入的user

@Test

public void testCheck() {

User user = new User();

user.setSname("java");

user.setPassword("java");

System.out.println(UserDao.check(user));

}

}

运行testInsert之后：

查询结果：