W dniu 2012-10-18 16:27, Stefan Kuhn pisze:
Hi everybody,
I want to get insert statements from a mysql database for a number of rows of
a table, specified by a where clause. I also need the inserts for the
datasets linked via foreign keys in other tables. So I need a sort of partial
dump, a bit like mysqldump, but restricted to a (small) set of data. This
needs to be done in a Java program, using mysql via jdbc.
Does anybody know an easy way to do this? Of course I could build the
statements in the java code, but I thought mysql might offer that or at least
parts of it. Does anybody have an idea?
Thanks,
Stefan
Hi there is a great java dbms client: SQL Workbench/J.
This app has some really great batch capabilities.
Look at chapter: 10,12,13,14
http://www.sql-workbench.net/manual/workbench-manual.html
I use it to generate insert files by specifying query files.
On the linux box i use simple shell script:
----------------------------------------------------------------------------
#!/bin/bash
#
# This is a script that fetch dataset based on '-script=file'.
# Dataset is saved as SQL INSERT file - this option, as well as
# output direcory can be also set in '-script=file'
#
# variables:
app_loc='-jar /yourPathToAppJar/sqlworkbench.jar '
jdbc_conn='-url=jdbc:mysql://127.0.0.1:3306/yourDatabase '
driver_type='-driver=com.mysql.jdbc.Driver '
db_user='-username=yourUsername '
db_pass='-password=yourPassword '
jdbc_loc='-driverjar=/jdbcPath/mysql-connector-java-3.1.14-bin.jar '
script_loc='-script=/pathToSqlScript/sampleSelectScript.sql '
logfile_loc='-logfile=/pathToLogfile/import.log'
java
${app_loc}${jdbc_conn}${driver_type}${db_user}${db_pass}${jdbc_loc}${script_loc}${logfile_loc}
# END of script
----------------------------------------------------------------------------
In the sampleSelectScript.sql you specify:
1) type of action (WbExport)
2) type of output (e.g. sqlinsert, can be also Excel file with
additional jars)
3) encoding (e.g utf-8)
4) output file name and location
5) target table in insert file
EXAMPLE:
----------------------------------------------------------------------------
-- BEGIN of SQL file
WbExport -type=sqlinsert -encoding=utf8 -file='/path/sampleInsert.sql'
-table=someTargetTable;
SELECT * FROM someTable; -- normal select statement with joins etc.
-- If you want specify another select statement in the same file, but
save it in another output file, you specify another WbExport:
WbExport -type=sqlinsert -encoding=utf8
-file='/differentPath/sampleInsert.sql' -table=someTargetTable;
use another_database; -- you can also switch between databases.
SELECT * FROM someTable; -- select statement
-- END of SQL file
----------------------------------------------------------------------------
Whole solution works great when combine with cron :)
I hope this solution will help you.
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
