Data type mapping and conversion between Java and MySQL

Java is a high-level programming language, and MySQL is a relational database management system. In Java programs, data interaction with the database is often required, and this involves the mapping and conversion of data types.

The mapping of Java data types to MySQL data types can be seen in the following table:

Java Data Types	MySQL Data Types
String	VARCHAR
int	INT
long	BIGINT
double	DOUBLE
float	FLOAT
boolean	TINYINT
Date	DATE
Time	TIME
Timestamp	DATETIME

As you can see, the data types commonly used in Java can correspond to the data types commonly used in MySQL. However, there are differences between some Java data types and MySQL data types that require conversion.

For example, conversion between the Date type in Java and the DATE type in MySQL. In Java, the Date type represents date and time, while in MySQL, the DATE type only represents date, not time. When converting, you need to pay attention to formatting issues.

The Date type in Java code can be formatted into a string using the SimpleDateFormat type, and then the string is passed to MySQL.

Date d = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String str = sdf.format(d);
PreparedStatement ps = conn.prepareStatement("INSERT INTO my_table (id, date) VALUES (?, ?)");
ps.setInt(1, 1);
ps.setString(2, str);
ps.executeUpdate();

When reading DATE type data from MySQL, you also need to use the corresponding formatting method to convert it into the Date type in Java, and then use it for program processing.

Another example is the conversion between the boolean type in Java and the TINYINT type in MySQL. In Java, the boolean type can only take two values, true or false, while in MySQL, the TINYINT type can store 0 or 1, or a larger range of integers.

In a Java program, when reading TINYINT type data in MySQL, type conversion is required.

ResultSet rs = stmt.executeQuery("SELECT * FROM my_table");
while (rs.next()) {
    int id = rs.getInt("id");
    boolean value = rs.getInt("value") == 1;
    ...
}

When reading MySQL data, you can use the getInt method to read the TINYINT type, and then use it to determine whether the value is 1, thereby obtaining the correct boolean type value.

When writing to MySQL, type conversion is also required.

boolean value = true;
PreparedStatement ps = conn.prepareStatement("INSERT INTO my_table (id, value) VALUES (?, ?)");
ps.setInt(1, 1);
ps.setInt(2, value ? 1 : 0);
ps.executeUpdate();

When writing a boolean type value to MySQL, it needs to be converted to int type, and true is converted to 1 and false is converted to 0.

In short, data type mapping and conversion between Java and MySQL is very important, and accurately handling this process is crucial to ensure the correctness and performance of the program. You can develop using Java development tools and MySQL tools to improve developer efficiency.

The above is the detailed content of Data type mapping and conversion between Java and MySQL. For more information, please follow other related articles on the PHP Chinese website!